Forms :: When Using Query With Grouped By Option / Can't Edit Record In Table
Aug 19, 2015
I have a table called "Workorders" that I have created a split form for and have customised it so that it had combo boxes etc to enable the user to change the record data.I then needed to add some existing queries to the form so that I could calculate some totals from records in a few other tables. This all works fine EXCEPT that I can no longer change any of the fields in the records.
I am sure that it is something to do with the fact that the 3 queries I added to the form use the "Grouped by" option and based upon my Googling, this is probably the issue.Here is one of the queries:
SELECT DISTINCTROW [Workorder Labor].WorkorderID, Sum([BillableHours]*[BillingRate]) AS [Labor Total]
FROM [Workorder Labor]
GROUP BY [Workorder Labor].WorkorderID;
I need to have the totals and I also need to have the ability to change the data..
View Replies
ADVERTISEMENT
Aug 22, 2013
I have a form that using "Query A" as data source. The Form need add (edit) a field value before save that Query Result to another Tabel. Is it possible to do that?
The PROCESS simply like below: Tabel A --> Query A --> Form -->Edit value a field -->Save to Tabel B
if it is possible, are there some requirements that have to be fullfilled?
View 2 Replies
View Related
Nov 19, 2014
After running an append query, the form created displays a blank/new record. How can I make the form display and ready to edit the appended record.
View 3 Replies
View Related
Feb 4, 2014
I have several comboboxes in my database from which users select values. However, in most cases, I do want to give them the option to add an item that does not appear while simultaneously updating the underlying table which stores those dropdown values. I have made use of the builtin "allow edit list items" feature of Access 2007 but the form always opens on the first record of the form on which they are to add the new item. Is there a way to specify that it should open in Data Entry view apart from using VBA?
View 4 Replies
View Related
Nov 8, 2014
I have a query which looks for like * surname*
in tblemployee fname lname dept active
this works fine and i can search using a requery button
however as deptartments are stored in tbldepts
when i change the query to retrieve the dept name instead of number directly from the table and i try to change this on the datasheet subform it changes it in tbldepts instead?
how can i change what dept the employee is in (as in change the number in tblemployee - but display the actual name?)
View 1 Replies
View Related
May 1, 2013
How do I get only one item in a combo box when using the Table/Query option (ie No Duplicates). I want only one of each field with no duplicates.
View 2 Replies
View Related
Mar 27, 2014
I have a continuous form with unbound controls. These controls write new records into a table using rec.addnew
If possible, I would like for the user to only be able to add/edit the new record only and not to edit the continuous form below. When I turn off edits on the form, I only get the new record to show. I found some pages that reference using code like .allowAdditons, .allowEdits and .allowDeletions but unsure of how to implement them since I have more than one control the user is entering data into. I also found another site that says to use a control to determine if the record is writable to: I do have a control that is set to =Date() on the default value.
View 13 Replies
View Related
Sep 19, 2013
I am attempting to create an attendance database that needs to be able to be updated if someone calls in sick.
I have created a cascading combo box where you can select a date up to 15 days into the past with the following code in the rowsource for cboDate
Code:
SELECT DISTINCT tblAttendance.dataDate FROM tblAttendance WHERE (((tblAttendance.dataDate)>Date()-15)) ORDER BY tblAttendance.dataDate DESC;
an after update event has been coded to show only employees who have been scheduled for the day
Code:
Private Sub cboDate_AfterUpdate()
On Error Resume Next
Me.cboAgentName.RowSource = "Select tblAttendance.agentName, tblAttendance.attendance,
[Code]....
So basically what i need to do is be able to use the cascading combo boxes to select the date and employee to go to that specific record so i can update the other fields. Currently when i attempt to do this with my current form, it automatically goes to a new record when i select a date and name and doesn't update the existing record.
View 1 Replies
View Related
Jan 3, 2014
I have created a form (races) with a subform (yachts in a race) to edit data in joined tables. It seems to be working well but I am having difficulty selecting which record to edit in the main form. I can scroll through the records until i get to the correct race but this won't work well when there are a large number of races to choose from.
I can see that I can use linked forms to select a race from a multi form or datasheet and call up my desired form by a double click on the ID field. This should work but isn't really elegant with users scrolling down a long datasheet to select the race record they want to work with. Is there a better way?
Races are uniquely identified by 3 attributes: SeriesDivision, Date & RaceNumber.I have created an index in the Race table for these 3 fields which forces them to be unique.I would love to create an interactive routine which asks users to select from a list for each of these 3 attributes and then returns the form for editing with the appropriate race record selected or even cascading - users select a SeriesDivision can then choose the valid dates for the selected SeriesDivision and can then select the valid raceNumbers for the selected date
View 1 Replies
View Related
Sep 2, 2013
I am trying to create a duplicate of a certain record in a table so that the user can then edit the new record - or old record even as it doesn't matter - as long as one of them is original. The idea is that any revisions / changes are stored the user may of made.
View 2 Replies
View Related
Sep 30, 2011
I'm creating a database of companies. Every company has at least one field of services they provide. There are three main groups which contain all the services (Let's say I have three tables).
What I need is to have a form/table (query?) where I can assign to a company any number of services from any group. Is it possible to do it in a form where I could simply add a service by choosing a group first and then a proper service, and then add more in the same way?
View 3 Replies
View Related
Apr 23, 2014
MS Access 2010 accdb - Using TempVars open forms either for New Record or Edit Record.
Two sets of forms - One set works perfect but the other will not accept the criteria for New Record
MsgBox checks confirm the Variables are still valid in the 2nd form Open Event yet the If Then does not work.
Just before the If Then, an invalid use of Null message appears.
View 2 Replies
View Related
Aug 9, 2014
I have a form which displays the records from a query (i have included a picture of this form) and allows me to filter them and create a report from the filter.
I'd like to be able to select a record with the record selectors (or preferably without) - and then go onto edit it in a new window and save the edit...
If possible also to select a record with or without record selectors and click a button to delete the record
searchdb.jpg
View 1 Replies
View Related
Jun 23, 2005
I've 2 tables:
customers
-id
-customer_name
-city_id
cities
-id
-city_name
I've a form to create new customers accounts.
The form has a drop-down control listing the cities in the "cities" table.
This drop-down control has a hidden column (id) and a visible column (city_name).
The user can choose a citie from the list and the city id (1st column in the drop-down list)
will be stored in the "city_id" field in the "customers" table.
Problem:
I need to allow users to enter cities not listed in the drop-down control.
When they entrer a new city, I need to create a new record in "cities" table for the new city.
I tried opening a dialog using the NotInList event, and the dialog pops up as espected, but also pops up an ugly msgbox wich says that the typed text isn't a listed item.
Please help :-(
View 1 Replies
View Related
Apr 30, 2014
My database was working fine. Now, if I have to save or edit a record, I get a message Run time error 2107. The value you entered does not meet the validation rule defined for the field or control. I am able to enter and edit new records in the table. I did not change any validation in form. Then it goes to debug mode and highlighted this code (me.recordsource=Listsql - is highlighted)
Private Sub ListSearch_Click()
Dim dBS As Database
Dim Rst As Recordset
Dim Listsql As String
Set dBS = CurrentDb()
Listsql = "Select * from MasterData where employeeid ='" & [Forms]![MASTERFORM]![ListSearch] & "'"
[Code] ....
View 3 Replies
View Related
Feb 25, 2014
I have a form with a duplicate record button.Sometimes when duplicating a record the duplicate is created but the form remains in edit mode (small pencil in the form margin).I also have a manual record counter on the form which does not refresh to the newest record number following the duplication? Both issues happen together and not independently.
I now have the standard MS Access record counter visible on the form to see whats happening and this always jumps correctly to a new record number when duplication is triggered, so when the problem happens the MS Access counter is showing one more than my own record counter.
If I use the standard MS Access record navigation buttons and go back and then forward by one record, the duplicated record is then corrected. The edit mode pencil is gone and both record counters read the same, which I assume has happened this has forced a save to happen
If Me.Dirty
Then Me.Dirty = False
End If
- in the current event of the form, would solve the problem, but it doesnt make any difference at all, its still no better, or no worse than before?Using CTRL + S removes the pencil, but doesn't correct my bespoke record counter?
View 6 Replies
View Related
Jun 30, 2015
I have been developing a form that will allow me to input, edit and delete data in a table.I have the input, delete, clear, and close command buttons working ok, but I am having trouble getting the edit function to work.
What happens is that I highlight the line in the table (shown on the form as a sub form) click the edit command button, and the field are populated with the current data. I then edit the field that I want, and the add, button, changes to Update. When I go to update I keep getting an error message "Cannot update 'ID'; field not updatable.Below is the code I am using, when I go to the debug the arrow points to the "WHERE ID=" line.
Private Sub cmdAdd_Click()
'when we click on button Add there are two options
'1. for insert
'2. for update
If Me.txtID.Tag & "" = "" Then
[code]...
View 14 Replies
View Related
May 13, 2015
I have recently updated several forms from Access 2003 to 2010.
On the old version I could 'hold down' the forward or backward pointers to quickly scroll through the individual records. Now I have to click each individual record to move one at a time.
Have I inadvertently changed a setting or is the quick scroll option no longer available?
View 2 Replies
View Related
Sep 10, 2014
Is there a way to duplicate, triplicate, a record based on an option group selection. i.e.
Form
Field: Option group
Value = 1
Value = 2
Value = 3
Field 2:
Field 3:
Field 4:
and so on.
Basically if value 1 is selected, then I fill out the rest of the fields hit enter and go to a new record. But if value 2 or 3 is selected, I fill out the the fields, hit enter. The information is recorded said number of times based on the selection in the option group. Ideally I would have2 or 3 of the same record in the db, with different Primary keys of course.
View 3 Replies
View Related
May 18, 2015
Currently running Access 2010.
I have a small database where we enter audit log data. In my add add screen from the switchboard, one of the fields is the user the audit is assigned to. So that our group wouldn't be typing their names a bunch of different ways I created a reference table called "Ref Data 005 - Contact Name". So i pre-populate the Contact Names such that they will be more consistent.
The add works perfectly. I built all my screens using the forms wizards.
Now for the modify routine. If the user wants to go in an modify one of the other data elements, I force them in the wizard to do this modify by choosing thier name. Therefore, when i built this screen using the wizard I reference the "ref table" mentioned above. I then throw open another form displaying all of the fields based on the records that it finds for that user when they choose the name from the Ref data 005 table. Hope that makes sense. Ie.. they choose the records they want to possibly modify by first choosing their name from the ref table.
It correctly pulls the records for whatever user you choose. However, when you make the update and save, it is changing the Ref table name as well.
So for example if three users were in my ref as :
bill
joe
john
And you launch the form, lets say joe shows up in the list, when i change it to bill to retrieve bill's records , it will do that but when i make the change, then Joe is changed to bill in the reference table.
View 4 Replies
View Related
Dec 12, 2013
I have a form to add, edit, and delete Records from a table. I am using the following VBA
Private Sub cmdEdit_Click()
If Not (Me.frmlEmpDetailsSub.Form.Recordset.EOF And Me.frmlEmpDetailsSub.Form.Recordset.BOF) Then
With Me.frmlEmpDetailsSub.Form.Recordset
Me.txtAddEditname = .Fields("Name")
Me.cboRoster = .Fields("Roster")
Me.cboPermFctn = .Fields("PermFctn")
End With
End If
End Sub
Instead of referring to the Subform to load the data i would like to refer to a combobox:
cboSearchName
Its not an issue but This Combobox contains 5 columns...
View 2 Replies
View Related
Dec 14, 2012
I have a table of items for our companies quotes. When we go to print out our quotes to send to the customers, the salespeople would like most of the items to be grouped under certain "headers" for the systems they are part of.
Ex:
Autopilot System
Part #1 Qty 2
Part #2 Qty 1
PA/GA System
Part #4 Qty 4
Part #5 Qty 1
My 'Items' table currently has these fields:
ID (PK)
System_ID (FK for 'Systems' table)
Part_No
Qty
etc...
'Systems' table has these:
ID (PK)
Sys_Description
I have a query using a RIGHT JOIN and a GROUP BY to tie everything together, I'm just not certain how to go about displaying the information the way I did above.
View 5 Replies
View Related
Jan 29, 2014
I have an Option Group I created (simple Yes-No) in a form that links to a Yes-No field in a table. I have the default set for this field to "Yes" in the form. The option button works when the form is first opened. If I cange the option to "No", that works. Here's the tricky part that is giving me eye twitches...
If I change the option back to "Yes", the radial button doesn't reflect the change...but it does change the table data! Why do the radial buttons stop changing? Even when I close the form and open it again, the data in the table is no longer reflected with the buttons in the form.
I have 64-bit, Access 2010.
View 11 Replies
View Related
Jul 14, 2013
Not all the choices (numeric codes) for my form's option box are being saved to the associated table.
I am using the options choices in the option group to enter data into a table and do additional things - clicking one option is supposed put a code in the table and then take you another form, while clicking a different option will make a new option box visible so I can collect additional data.
My option group has three options, only the data from the second option, which makes a second option group visible, is being saved in the table. If someone clicks on the first or thrid option, which takes them to another form, the data from that option choice does not get saved to the table.
View 2 Replies
View Related
Oct 20, 2014
I have an Excel file with a name range "DBIAS" which identifies all database data.
Then I have an Access file with a form to import that database (better, that named range) into an Access table. While importing, I have to filter some records or grouping by some field.
I cannot run correctly a VBA code to get data (filtered and/or grouped) from that name range and save those records to an existing or a brand new table.
I could get those data as DAO.recordset and printed out with "Debug.print" on immediate window, but I cannot complete the final step: writing those records to a table.
View 3 Replies
View Related
Mar 14, 2013
I have a database that tracks students and their test scores. I am trying to figure out a way to where it will pull the student's latest test score and compare it to see if they fall within standards. So far I have 3 tables.
tblStudents
studentID
studentName
studentEmail
tblResults
resultID
resultStudent
resultDate
resultTest
resultScore
tblTests
testID
testName
testPassScore
The query I have written looks like this: SELECT tblStudents.studentID, Max(tblResults.resultDate) FROM tblStudents LEFT JOIN tblResults ON tblStudents.studentID = tblResults.resultStudent GROUP BY tblStudents.studentID." The data pulls just fine at this point. What I'm trying to figure out is how to then add more columns to this query to start doing comparisons. When I try to add more columns it tries to use them for additional grouping and adds many more records.
View 8 Replies
View Related
