VBA code to DSUM

[databasesrus]

I am trying desperately to total inventory levels. I have tried update queries and DSum's in my form. Something is definately not right.

I have a table which lists starting quanitities. I have another table in which via form users are entering inventory changes.

How can I just have the current total after changes?

I need the starting quanting + quantity received - quantity processed - quantity rejected - quantitity shipped


This is the final piece of my entire database so it is critical...

Any suggestions, comments, etc. would be greatly appreciated! Thank you!

 

[scriverb]

You seem to have designed the tables correctly. I am referring to the one-to-many relationship without having a field for the current balance. This is a value that should be calculated and not a hard value in a inventory record.

If the transaction records are stored as minus values then for a form control you could use this as the Control Source:
=DLookUp("[Starting_Balance]", "tblInvStart", "[Product_ID] = '" & Me.[Product_ID] & "'&quot - DSum("[Trans_Amt]", "tblInvTrans", "[Product_ID] = '" & Me.[Product_ID] & "'&quot

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???