I have a continuous form that that displays multiple records and each record is its own line. This form uses Table1 as its datasource. Each line/record has a field called QTY (from Table2) and NEWQTY. There is one calculation done in an AFTER UPDATE that takes QTY from Table 2 and subtracts SHOTS USED from it-this new number is called NEWQTY. I want to replace QTY in Table2 with the NEWQTY for each record (see attached JPG). The form can have up to 20 records showing.
In the attached picture:
Name, Code,QTY - come from Table2
QTY - user inputs data
NEWQTY - calculated when CALCULATE button pushed.
Three things need to happen when the user CLICKS the CALCULATE button:
1:Calculate NEWQTY for each record - (I have this)
2:After calculate, replace QTY from Table 2 with NEWQTY for EACH/ALL records. - (need this_
3:ADD ALL current records to a different table - (I have this done)
When the user opens the form again to input data the new values for QTY should show i.e instead of 4 Buck the second user should see 3, the second user should see 15 00 instead of 25.
I need VBA code or code/query to make this happen and be transparent to the user.
Thanks for the help