I have a form to enter records in a table of staff attending training courses, in the form is a sub-form based on a query from a different table. When a staff number is entered on the main form the staff name grade etc appears on the sub-form, my problem is that I need the table to be updated from the main form and the sub-form. Is there a way of doing this?
Thanks
Hi guys, wondering if you can help because no one I've talked to can so far..
I've got a form with Room information on, and on it a subform. The subform contains information from 2 tables (Items and Audit). Items is basically a list of all the different combinations of furniture item that can exsist (a damaged green chair, a large new desk etc) and as theres only about 70, and 1000's of bits of furniture I decided having common entries would be a better method to save on memory.
Right, the Audit table contains a Room# field and an ItemID field, obviously each furniture item within the Items table has a unique ItemID. The subform is created displaying the information from the Items table, so it tells you that its a damaged green chair rather than just giving you the number 3, and has the ItemID hidden, just for linking purposes.
This works fine for displaying information, but I can't make it so the user can add a new record. If a new furniture item needs to be added then they want to be able to type (new blue chair) and not the ItemID number, so the subform somehow needs to lookup the ItemID from the information typed in as a new record, and then write this ItemID into the Audit table, before refreshing the form. Obviously though at the minute, if anything is added to the subform, it just goes onto the bottom of the Items table, and hence repeats whats already there, which is silly.
I'm just not sure how to go about doing it and would appreciate any help or advice that can be offered. If I've not explained clearly enough please let me know.
Thanks!
I have a subform in which I use the following statement =forms!frmetc.model It is picking up data that is listed in the form that it is a child of. It picks up the data just fine and shows it properly, but the fields in the table associated with it remain blank. Any suggestions of the right was to accomplish this task? Thank you!
View 1 Replies View RelatedI have a form that users enter billing logs in. There is a button users can click to the right of their billing data that opens another form where they can enter details regarding the billing log. Beside this button is a check box that shows them if details for this particular billing log has been created.
I can not for the life of me work out how to get the check box to update with a check after I enter Billing details without opening and closing the form again. I felt I have tried requery and refresh in all ways I have seen here, yet it is not working.
The main form is frmDslEnter and the billing log is entered in a subform from here called tblSessions subform. The checkbox on this subform is called chkProgressEntered, and it's control source is =Not IsNull([DSLIDNumber]). I tried putting the refresh on the Close button on the Billing Details page, on the Got Focus of the subform and the main form and haven't had any luck.
I'm using Access 2010 and need to update a sub form in a form that is within a navigation form. So it goes:
Navigation Form > Main Form > Subform
How do I do this? I've attached the following to a click event of a cmd button but I just can't get it to work:
Forms!frmMain.NavigationSubform.Form.frmJobHeadSub Form.Requery
Hi,
Mainform and Subform are linked on field "barcode".
Barcode is the primary key for the table that Mainform is based on.
When user is entering a new record, I have the BeforeUpdate event of Mainform.barcode set to check to see if the barcode already exists in the table, and if so, to give the user some meaningful error message.
Works fine, except that the subform still updates to match the invalid, previously-used barcode. I want the subform to stay blank until the user has entered valid data in MainForm.
How do I accomplish this?
Thanks for any ideas.
Hi, I have a problem I don't know how to solve it. I have a subform in datasheet view, linked to the main form. There is a running total on the main form based on a dsum on a field of the subform.
When I delete a row on the subform datasheet, the main form calculation is not automatically updated. I had to create a "update" button to do the refresh.
Is there a "on record delete" event available for a datasheet sub form?
Thank you for any help.
I have a navigation tab with 6 sections, of the 6, 5 of them are self-made split view with a form view on top and datasheet on bottom and upon adding records through a command button on form view I would like the datasheets to update.
On other forms it would (I believe) update to the bottom of the datasheet, now on the ones I've been banging my head against it either replaces the top record or doesn't show up unless I switch tabs and switch back. From my searches I assume this is in need of a requery...
Within my main form I have a combo box called "workgroup." Also in the main form is a subform called "sfrmSubmissionRecords," and within this subform is a combo box called "covermemo."
Each cover memo is assigned to a specific workgroup, so my intention is for each time a new workgroup is selected from the dropdown, the covermemo combo box in the subform becomes populated only with the covermemos associated with that workgroup. I'm almost finished except for the final step when I try to make the values regenerate...Access says that can't find the referenced form "sfrmSubmissionRecords"
This is the code that I'm using:
Private Sub cmbWorkgroup_AfterUpdate()
Forms![sfrmSubmissionRecords]![cmbCovermemo].Requery
End Sub
I'm supporting some Access/VBA code which includes the following behavior:
When an update is made in a (linked) child form, the current date/time is written to a textbox on the main form via
Me.Parent!txtMod = Now
Likewise, the BeforeUpdate event of the main form updates the same field (and then does a bunch of form validity checks, possibly followed by a Save).
When we upgraded to Access 2003 from Access 2000, a problem erupted:
When the user returns to the main form (by clicking on any control outside the subform), the following message is issued: "This record has been changed by another user since you started editing it (etc.)..."
At this point the user must choose between "Save Record", "Copy to Clipboard" and "Drop Changes".
Conceptually, I see why this is happening (though I wonder why Access 2000 didn't flag it). The subform is the "other user". When the main form is returned to, it is "Dirty" (unsaved update), so the main form's BeforeUpdate event fires, and attempts to update the field again (Me.txtMod = Now). Since there is a pending change to the field, an error dialog pops up.
As a quick fix, I replaced the code in the subform with a flag ("Hey, I've been updated!"), which the main form checks on the Exit event of the subform control. But that seems a horrible solution. Surely there's a better way?
Notes:
1. It would be preferable to have the timestamp updated right away by the subform, though having it updated when the subform loses focus is better than nothing.
2. Since a subform is a control from the main form's perspective, I'm surprised that a change to that control doesn't render the main form "Dirty". I guess from a database standpoint it makes sense (the form's table isn't getting updated at all; just the subform's). But then, how does the main form know that the subform has been updated?
3. I tried catching the error via Form_Error. I can make the dialog not appear via "Response = acDataErrContinue", but unfortunately it defaults to dropping the changes (old and new), rather than saving the new.
How would I go about updating a table from a form? My table consists of employee contact information, ie.. Cell phone, pager number, home phone.
I have created a forum with unbound combo box called employee. As I choose the employee name all other fields are populating just as i want. After I update the form with the correct system contact information i want to be able to save the data and have it update the table. It appears the only data not updating the table is the anything in the combo boxes that are unbounded. Any suggestions?
If you open the attached sample db you will see that I have a cascading combo box (It is working fine).
The data for the tables that are the source for the combo box are imported from an excel sheet.(This works fine)
Now my problem the data from excel is variable after the data is imported into access I want to add to it items from another table.
After the data is imported into both tblModel and TblParts I want to append to tblParts each item from tblFixed for every Model.
If you open tblPartsEXAMPLE you will see what I want to do (I added this manually).
I hope that my expanation is clear and someone can offer some advice.
Thank you in advance.
alright I have a form and I need a couple of text boxes on there to add information to a different table than the other ones. I found this code somewhere on this forum can you tell me if I can use it for what I need, A more detailed explination of my project might be nessecary.
Code: sub AddRecord_Onclick docmd.setwarning false if not isnull(txtName1) then docmd.runsql "INSERT INTO table (areaname) VALUES('" & txtName1 & "');" txtName1.Value = Empty endif end sub
what is the proper syntex for the Insert INTO part, Table is obviously the table I want it inserted into, what is (area), is this the field I want it inserted into?
I want to create a form that will update a table. I have two columns, an item number and a paid column. On the form I want to plug in the item number and then I want to hit ok and it will find that item number and put a value (for example "X") in the paid column.
I have tried running an update query when i hit the ok button but I can never get to work
I am having trouble figuring out the method to automatically update some fields in SubForm from 2 other SubForms.I have attached 2 pics, the first GradeEntry1 shows what the tblTopic_Class_Grade form looks like after I manually enter everything into it. GradeEntry2 is what the form looks like when I fill out the Form starting at the top.
I'd like the tblTopics_Class_Grade form auto-populate the TrainingClassID (it currently does this), TopicClassID, StudentID, TrainingTopicID based off the entry from the above forms.My end goal is that I need to have a grade for each student on each training topic for each class. Like:
Class1-Student1-Topic1-GradeX
Class1-Student1-Topic2-GradeX
Class1-Student2-Topic1-GradeX
Class1-Student2-Topic1-GradeX
I've creating an Access project, which is at an early stage. I'm using Access 2003 with a default file format set to Access 2000.
I've been trying to update a date field within a table using this piece of code from a Calendar form. The Calendar form has a Command Button called cmd_Accept. Its sub routine is shown below. Both variables, dt (date) and fvr_id (field visit report ID) are global.
The code works - too well! Instead of updating the only the target record, it will either update the first record plus the target record or all records with the same date and I cannot understand why this should happen.
[Field Visit Report ID] is unique, being the primary key for the table, tbl_field_visit. It is AutoNumber generated. I've double-checked the table and all entries in [Field Visit Report ID] are unique. All my MsgBOX displays show what I would expect to see.
As you can see, I've also tried doing the same task using SQL - with exactly the same result.
I've been going round and round with this for days. Should I bin this and go down another route, or can anyone tell me where I'm going wrong?
Any help much appreciated.
Private Sub cmd_Accept_Click()
Dim dbs As Database, rst As Recordset
Dim strSQL As String
dt = ocxCalendar.Value
' +++++++++++++++++++++
' Field Visit Report ID now set from another sub routine. Tested and working.
' fvr_id = Form.[Field Visit Report ID]
' +++++++++++++++++++++
' MsgBox "The Field visit report ID is " & fvr_id, vbOKOnly
' MsgBox "The date is " & dt, vbOKOnly
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_field_visit")
If rst.RecordCount <> 0 Then rst.MoveFirst
MsgBox "fvr_id = " & fvr_id & " Date = " & dt, vbOKOnly
Do While Not rst.EOF
If (rst![Field Visit Report ID] = fvr_id) Then
MsgBox "The Record ID is " & fvr_id, vbOKOnly
found = True
With rst
.Edit
!Date = dt
.Update
.Bookmark = .LastModified
End With
End If
rst.MoveNext
Loop
rst.Close
' If found Then
' vbButtons = vbOKOnly + vbExclamation
' MsgBox "Record Found! " & " Date = " & dt & " Field Visit Report ID = " & fvr_id, vbButtons
' If rst.RecordCount <> 0 Then rst.MoveFirst
' strSQL = "SELECT tbl_Field_Visit.Date FROM tbl_Field_Visit WHERE tbl_Field_Visit.[Field Visit Report ID] = " & fvr_id
' Me.RecordSource = strSQL
' MsgBox strSQL, vbOKOnly
' strSQL = "INSERT INTO tbl_Books ([Date], VALUES '" & dt & "')"
' strSQL = "UPDATE tbl_Field_Visit SET tbl_Field_Visit.Date = '" & dt & "' WHERE tbl_Field_Visit.[Field Visit Report ID] = " & fvr_id
' MsgBox "strSQL = " & strSQL, vbOKOnly
' dbs.Execute strSQL
' End If
dbs.Close
Set rst = Nothing
Set dbs = Nothing
fvr_id = 0
DoCmd.Close
End Sub
Can anyone help?
I'm having trouble storing autofilled data from one of my forms and I was wondering if anyone has any ideas.
My data is as follows
tblProducts
ProductID
Description
Nett
tblPurchaseOrders
PurchaseOrderID
PODate
Supplier
tblPurchaseOrderDetail
PurchaseOrderID
ProductID
Description
Qty
Nett
Now I have a standard order entry form which works fine, my problem is with the subform:
frmPurchaseOrderDetail
ProductID
Description
Qty
Nett
On my form Description and Nett are auto populated referencing ProductID but this information does not transfer to the table tblPurchaseOrderDetail. The main problem is that a nett price can occasionally chanege (inflation) but I need to be able to look back to a particular order for audit purposes and be able to see what I paid.
I'm sure there is w way round this, but I've been unable to figure it out. I've gone through loads of different threads on the forum and as a result am sure it can be done, I've also looked at the examples posted by several users, but none seem to approach the lookup quite from this angle, does any one have any idea...sorry for the long post, thanks.
Hi all
I'd be very thankful if someone can please help me with this problem.
I have 1 Table. This Table is imported and contains data from Excel. It also contains fields I added manually. There is about 20 imported fields (full of data) and about 100 manually added fields.
Then I made a Query of this table. I made some calculations in this Query.
I then created a Form based on the Query. I then made a Combo Box. This Combo Box selects a customer, and then updates the other fields (the ones I imported from Excel) depending on the customer I selected.
So now what I want to do is this: I want the fields (updated from the query) and newly entered data to save to another newly-created table. Can someone please help me with this? Any ideas?
Thanks a lot. All input greatly appreciated.
kruger101
Help - I'm at a loss! I have a form that I am using for invoicing for a Trucking firm. On this form, I have three fields that I am having problems with. These fields are From, To and Mileage from a table named Location. The user selects "From" from a combo box then goes to select "To" from another combo box. The "To" field is a filtered list of only those locations which correspond to the "From" that was chosen. Once this is done, the corresponding mileage is automatically filled in and used for rate calculations. If the "To" that they want is not in the table, they can double click the field and a form to add a record to the table pops up. So far, so good.
My problem is that now the user wants to be able to add a record such as New York to be From and Miami to be To with 123 as mileage once and be able to have Miami as From and New York as To with 123 as the mileage if he needs on future invoices. Can this be done without having to enter it again?
I have a database with nine tables, and forms for data entry, no subforms. It is created for students to track all work required to complete their degree. When student information in added for a new student I need to update other tables with their student id.
The student information table has student ID defined as autonumber. How do I update the other tables with the value generated here, and should all the tables with student ID be defined as autonumber, or some other data definition?
Thank you.
I need to add more entrys to my form, but when I go to the ref. table it won't let me. I states too many fileds defined.
Another table lets me add to it but when I go to the form it does not show what I added.
Hope I make sense.
Thank you in advance
Greetings! This is my first post.
I have two tables: 1) tblClient, 2) tblCase.
Client records from tblClient contain a field called Client_CID (Primary Key), as the Client ID. There are also fields Client_HIGH_FILE_NO, a numeric value of the last case number assigned to the specific client and Client_PREFIX that contain a unique three letter prefix that identifies the client.
Case records from tblCase contain a field called Case_CFN (Primary Key), as the Case File Number. tblCase also contains a field called Client_CID that contains the Client ID associated with the case (obtained from a combo box lookup from tblClient).
My form is frmCase bound to tblCase.
The Case_CFN is constructed by combining the value of the selected Client_CID’s Client_PREFIX with the value of Client_HIGH_FILE_NO plus 1.
I am constructing the Case_CFN on the before update event of the combo box for selecting the client. The resulting Case_CFN may appear as follows
ABC10001
Where Client_PREFIX = “ABC” and Client_HIGH_FILE_NO = 10000
Now, I need to increment Client_HIGH_FILE_NO in tblClient by 1, meaning I need to set the value of Client_HIGH_FILE_NO for the selected Client_CID to, in this example, to 10001.
Questions:
1) Is anyone familiar with this type of number scheme generally and if so any ideas?
2) Can anyone tell me how to update the value Client_HIGH_FILE_NO for the selected Client_CID?
I have a table holding clients data, I need it to work out the age of someone when an application is made, which I would like to be stored on said table. I have two fields [DOB] and [signed date], which I have used created a query with and an (unbooud?) field called age at application with the expression =DateDiff("yyyy",[DOB],[Date signed])
This works fine when I run the query, but I am unsure of the new next step of how to commit it to the table and even if that's possible.Ideally I would like this to run behind a form maybe using some click event after the [signed date] field has been entered.
I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'
Private Sub cmdQuote_Click()
'Creates quote date and prints quote
Me.QuoteDate = Now()
Me.cbAgentID.Requery
DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID
End Sub
When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.
I have a main form and a subform.
Both forms have the field called JobID in common.
Both forms have a field called JobStatus.
Any easiest solution so that After I Update the field called JobStatus in the subform, it changes the field called JobStatus in the main form to the value which was selected from the subform?.
Hi,
I have two tables, tblCountry and tblLocation. With the following structure
tblCountry
ID
Name
Text
tblLocation
ID
Country_fk
Name
Text
As you can guess tblCountry lists all of the countries, tblLocation lists all of the locations in each country, the tblLocation.Country_fk field is linked to tblCountry.ID.
I want the user to be able to edit [tblLocation].[Text] using a form. They simply select the country and then the location using combo-boxes and then add or edit the content using a textbox.
In my form I have a combo-box that displays the country names, the RowSource is set to
SELECT [tblCountry].[ID], [tblCountry].[Name], FROM tblCountry ORDER BY [Name];
I then have a second combo-box that lists the locations for the selected country. This uses an AfterUpdate() procedure to select [tblLocation].[ID] using an SQL query based on the value of the country combo-box. I.e:
SELECT [tblLocation].[ID],[tblLocation].[Name],[tblLocation].[Text] FROM tblLocation WHERE [Country_fk] = " & Me.country_box.Value & " Order By [Name]"
I want to be able to have a textbox that then displays [tblLocation].[Text] for the selected location. Thats where the problem arises. I can't find a way that will let me display any content thats available for the selected location AND let me edit it. I've tried using UpdateAfter procedures, different bindings (tables, queries based on the value of location combo-box).
Can anyone suggest how I can display [tblLocation].[Text] based on the value of the selected country/location and be able to update the information via a textbox?
Any help would be appreciated!!!
Thanks
Jon