Multiple Record Reference on Subform

[jschmidt]

Hello,

I have an inventory/ordering database I have been working on for some time now and I have some accross a part that I am not sure how to deal with. I am using access 2003 for this database.

I have a main form where the user is able to enter the shipping and po number and ... ... on the main form there is also a subform which allows the user to select the products that they need to include for the order.

On the subform the user can select multiple products to be included in their order and they can enter the quantity as well. I have a button that the user can click to process the order and print the invoice. What I want to do is have the button, on the main form, take the quantity from the subform and deduct that from the quantity on hand in the product table so that inventory is always up to date.

Current problem:
Product 1 on the subform has a quantity ordered value of 13.
Product 2 has a quantity ordered value of 25.
When I click the button Product 1's quantity on hand is deducted by 13 and so to is Product 2's quantity on hand. Product 2 should have been deducted by 13.

Help?

[nico5038]

Hmm, the solution of direct stock manipulation has a big back draw. Ofter orders are changed on the last moment and stock might be different as recorded.

My approach is to register the stock and count dynamically the quantity remaining by subtracting all delivered (final) orders and add all deliveries.
Once in a while I produce a stock check report with the administrative stock quantity and that's used to check upon the true stock level. Thus differences between the real stock and the administrative quantity are straightened out and the "leakage" can be made visible.

Getting the idea?

Nic;o)