Hi

I'm building a database to record the order of consumables and quantities in stock for each item ordered.
I have created a main Order Form with details of the Supplier, OrderNo, Dates, etc and have a Subform for the Order Details.

The above use 2 seperate tables Ordertab (Order form) and Orddetails (Order details subform), linked via "OrderNo". The 'Orddetails' table has 2 primary keys, OrderNo (same as Ordertab) and ItemID, which links this table to the "Products" table.

When recording an order I use the Order details subform, selecting a Product Code from a combo box (listing all products used) which then uses a DLookup to populate the remaining fields in the subform (ItemID, Description, Cost) resourcing the Products table for the values.



My Problem

I then add a "QTY" and I need this Qty value to be added to the Qty value already stored in the Products table (or an alternative table?) giving me a total stock for this ItemID.
Please could someone advise me of the best way to achieve this function?

Many thanks

Mark