Best Way to handle stock.

[softbrix2]

I need to handle the stock on hand for each purchase made. 1 purchase can have muitlple products with each product have diferent qty. Currently I have a stock table that keep the stock on hand, in my order form after update of each qty field I do a subtraction from the stock table via mysql.
DoCmd.RunSQL "UPDATE products SET Stock=Stock - " & Forms!Orders![Order Details Subform].Form!Quantity & " WHERE ProductID=" & Forms!Orders![Order Details Subform].Form!ProductID

the problem I have with this is that I need to do a insert for each product ordered, currently its only doing it for the last product. Is there a better way to handle stock? if so can someone please point me to an example where they are checks and balances for the quantity sold/remain

 

[seaport]

A sample database "Orders" shipped with Office 2000 (I'm not sure it comes with Office 2003 or not) deals with the same situation as yours. You may check it out.

Seaport

 

[Hafa1961]

Read Allen Browne's Inventory Control: Quantity on Hand

http://allenbrowne.com/AppInventory.html

 

[missinglinq]

Or, from the horse's mouth itself:

http://office.microsoft.com/en-au/templates/CT061995421033.aspx

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[softbrix2]

ok i've looked at the office 2000 inventory control but I don't see how if calc total sold compages to total ordered/on hand.