I really want the total to be updating as each new order item is added to the first subform. I've looked closer at the query that feeds the Total Firkins In Period field and the problem seems to be that using the After Update event in the subform doesn't cause it to update. Before closing, even moving the focus out to another control on the form doesn't do an update. I doubt you need to be concerned with the form's Current Record property.
Using only Microsoft Access, a user has to fill up a form with the relevant details so that a new entry will be appended to a data table. A new shop branch will have the fields Shop ID, Shop Name, and Person In Charge.
Upon filling up a form with these 3 details, a user will click a button "Submit". I have tried linking the button to run a query that updates the data table but no new entries are created.
For example, you have ID of record that should be updated in the form data set, so you can write something like: 2) You can look at the Bookmark property - both Recordset and Form has this property, it describes the record position.
So you can write something like this (not the best example, but can help you to get an idea): Consider a simpler approach.
I have an Orders form with subform to show existing purchases and allow new ones to be added. Recalc, which gave Run-time error 438, 'Object doesn't support this property or method' - Forms! Customer ID HAVING (((qry Firkins By Customer Within Date Range. [Orders].[Customer ID])); The previous query, qry Firkins By Customer In Date Range has SQL SELECT qry Firkins Total Per Order. Achieving a target level qualifies them for a discount, and adding in the current order may be important if it allows the target to be reached.
An unbound field Total Firkins In Period on the main form shows the total volumes purchased over a defined period up to the current date, using source =DLook Up("Total In Period","qry Firkins Total In Period") When adding new items to the subform I want this field to update after each new entry. I tried including in the subform's After Update event - Me. My Total Firkin In Period field calculates correctly for orders up as far as the current one but isn't then updating for new items. First added a new order with a single order item that should have increased the total by 1. DLook Up("Total In Period","qry Firkins Total In Period") gave the original total (as displayed on the form) without the extra 1. Just to be clear, I'm creating a new order and add purchases in the subform, which is in datasheet view. Move Next Loop End With 'Variable volume levels - set discount % based on total firkin equivalents in order If TF 0 Then !
The VBA to get the index of the record in the form is as follows. Execute str SQL1, db Fail On Error str SQL2 = "UPDATE job SET JOB_NEXT_OCCURANCE = JOB_NEXT_OCCURANCE JOB_RECURRANCE_RATE WHERE job. @Hans Up, I get what you are saying, but I dont quite think it's applicable (I did not provide enough information first time around for you to understand I think) @sarh I believe this Recordset that you are talking about is what I need, however I cannot figure out how to use it, any hints?
This is my first shot at VBA, and after an hour of searching I cannot find anything to solve my problem. Working in Microsoft Access 2007 Further Info All tables are normalized Vehicle Table: Contains vehicle_id(pk), as well as rego and model etc Job Table: Contains job_id(pk), vehicle_id(fk) and other info about what needs to happen, as well as the next occurance date, days between each occurance of the job (all jobs repeat) and other info Job History Table: Contains job_history_id(pk), job_id(fk), date completed and comments When the job completed button is pressed, it should create a new entry in the job history table with the current date, any comments and the job id This is the script I am trying to get working Private Sub Command29_Click() Dim str SQL1 As String Dim str SQL2 As String Set Rs = Current Rs Set db = Current Db str SQL1 = "INSERT INTO completed_jobs(JOB_ID, DATE_COMPLETED, COMMENTS) VALUES " & Rs! @Matt I am 90% sure I am using a bound form (Like I said, new to Access, been looking at everything people have suggested and learning as I go).
If you need to update some field of underlying dataset, you can write something like If your form has control bound to "Some Field", then the form will be updated automatically.
If this will not help, you can look to a couple of another directions: 1) Update records using SQL code.
Dirty = False' before the requery to force a save but this also didn't make the total update.