Calculated field on form needs to calculate when form opens for all child records

[robsworld78]

Hi, I've got a bit of an issue with a calculated textbox on a form. Everything is working on the form fine and the calculation does work if a user enters a number in another textbox but what I want to happen is when the form opens the textbox does its calculation based on the values in the other textboxs corresponding to each record.

Here's some history on the form.

The form is called "NightCount" and has 3 subforms. The main form holds the vendor info, the first subform holds the Order Info, and the 2nd and 3rd subform hold the order details. These are the records that have the calculation.

The main form and Order Info subform are linked via VendorID, the order info subform and 2 order detail subforms are linked via OrderID and those 2 subforms are linked together via a record source using InventoryID.

The 1st order details subform has the product name and a textbox labeled BarsGiven. BarsGiven comes from a query which is actually named "BarsLeft" in the query and is the running balance for that product type. Because this query isn't updateable I had to make another subform for the rest of the order details which need to be updated.

On the 2nd order details subform theres only one textbox and its called "BarsReturned". In the "AfterUpdate" event for this textbox I have a calculation taking place, it takes the "BarsGiven" from the 1st subform and subtracts it from whatever is put in "BarsReturned" and the answer is put in "BarsSold" which isn't on the form but in the table the rest of the info is. So yeah a calculated field being stored, I know the rules and this works, nothing duplicate being stored, etc...

The table for these subforms is called "Inventory" (I know not the best choice of names). Don't be confused by the field called "BarsGiven" its not whats on the "NightCount" form, this is for new product given that day, the one showing on the Night Count form is the running balance from the query.

Ok so that's the form broke down and now here's what's currently happening.

I attached the db to make life easier for getting help so here's what to do to see what you need to see.

First open the table "Inventory", scroll to the bottom of the table, the last 4 records are for the order I have ready. They are OrderID 10. The last field is "BarsReturned" you'll see there is no number in the field at this point. Now look at the field "BarsSold" you'll see the 4 records have a 0. All is good at this point.

Now keep the table open in the background and have the "BarsReturned" field to the far right of your screen so you can still see the blank fields.

Now in the navigation pane go to "NightCount" form and open it, when it prompts for name enter "11". Before pressing enter keep your eyes on the table in the background on the "BarsReturned" field and see what happens. You'll notice all the blanks for the order automatically change to a 0 as soon as the form opens. Great.

Now move the form over to the left so you can see move of the table and notice that the field "BarsSold" still shows 0 for all the records for that order. Now on the form enter a 0 in all the records under "BarsReturned" and watch the "BarsSold" field in the table, you'll see it start to populate. Great.

Now this is what I need it to do. Once the form opens and those 0's get thrown in the the table and form I need the calculation for all those records to happen at that time too just like if the user entered in the 0.

When you open the NightCount form and see the 0's go in the table that's when the BarsSold field should also change.

The math is located in the "AfterUpdate" event of the "BarsReturned" textbox on the form. I've tried putting that math in a lot of other places but it won't do it, I've tried requery in there as well but nothing. The closest I can get is by putting the "AfterUpdate" event for BarsReturned in the "OnEnter" event for "BarsReturned. When I do that after the form opens if I click off the first record it pops a questions about 2 people editting at the same time and prompts for a save. If I had save on the first screen and drop changes on the second prompts it will almost do the math. If you click in a record and out of it the math takes place without having to reenter that 0 so I know the 0 being loaded when the form opens will do the math it just needs to be requeried or whatever at the right time.

After you open the form don't use the post buttons on the form, I have a few because of trying different things. If you click one of the them it will change the status of the order to review which you would need to turn back to "On Road" to see the order again in the Night Count form, so don't click the buttons just close the form with the X to make life easier. Also after loading and closing the form you need to reset the values in the Inventory table. Blank out the field "BarsReturned" on the last 4 records and set "BarsSold" back to 0 for the last 4 records. If you don't and reopen the form the numbers will be different.

Well I said enough I'll make it more confusing then it is.

Oh its a success if you open the "NightCount" form and without doing anything you see in the table Inventory in the background that the "BarsSold" changed to around 30's then all is how I want it. If the "BarsSold" stays 0 when the form opens like it is now its not working.

I attached the db, its split so I've joined to be in C:\Inventory Control, You need Access 2010


If anyone wants to help that would be awesome, I'll be sure to give back to the community in time.

Thanks

 

[Mr. B]

Try using a query to produce the records where you need the calculation. Create the caluclation in the query.

 

[robsworld78]

Thanks for the suggestion but its been solved, someone figured it out for me. This is the code I put in the "OnLoad" event of the subform.

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone 
While Not rs.EOF
      DoCmd.SetWarnings False 
      DoCmd.RunSQL "UPDATE Inventory SET BarsSold=" & rs!BarsLeft & " WHERE InventoryID=" & rs!InventoryID     
      DoCmd.SetWarnings True
      rs.MoveNext 
Wend 
rs.Close