Update Quanity on Hand

[RamanS]

Hi,

I am creating an inventory management database.
I have three tables in this database, Products, Orders and Order Details.

I have a form Order Entry in which i create an order to be shipped to a client.
This form is created using a query that combines all fields of Orders and Order Details table.

The upper part of the order entry form is used to enter basic information such as the order date, client name, address etc.

The lower part of the Order entry form contains a subform (datasheet), where i enter order details.

There are two columns in the order entry subform

products and quantity

the product column actually allows you to select the product from a drop-down list (containing all products) and the quantiy column enables you to enter the total quantity of product shipped to the client.

there is also an Update Inventory button on order entry form.

Now, all i need to do is that after i finish selecting the products and specifying the quantity i would like to ship, I want to click the Update Inventory button, to deduct all the quantities of the products i selected from the Products table.

for example,

i selected D123 as product, and 12 as quantity,

clicking the update button should lookup the product table, search D123, and substract the quantity that is there.

I could have done this if i selected one product, but in the subform, i can select mutiple products. is there a way i can loop throught the products selected, and then deduct quantities.

[MMcCarthy]

Put the update code in the after update event of the quantity table. However, this is dangerous as if the user changes the quantity you have a problem.

The reality is this is the reason why a calculated field should not be stored. The correct way of handling inventory is to have a tblProductReceived showing a new record for each product recieved and its quantity and tblProductSold showing a new record for each product sold and its quantity.
A query then written based on these two tables should show quantity on hand at all times.