Updating a Running Total field after entering a new Item
Posted on 2011-10-26
I have a form where the user will enter a new record, which contains among other things a field for TotalLBS, and a field for LotNumber. This form is based on a table called tblSHInv
I have a table that holds infomation about LOTS called tblLots (LotNumb, StartDate, EndDate, TotalLotLBS)
When the user saves the record, I would like to take the TotalLBS from the new record in tblSHInv and add it to the TotalLotLBS field of the current LotNumb in tblLots.
My question is, is there a better way to update the TotalLotLBS value other than building a query and then running that query when they save the record?
Also wondering if I'm missing something in keeping a running total of an item in a field.... or should I build some sort of transaction table and total from that. Looking for correct method.
Thanks for any help.