Updating Inventory

[Guest]

I have an inventory table (fields: item, sotckno, quantity on hand) and an
orders table (fields: items, quantity, date, etc.). What is a simple way to
have a form based on orders that deducts from the quantity on hand in the
inventory?

 

[Allen Browne]

Kathy, that's not as simple a question as you might think.

If you use the AfterUpdate event procedure of the form to subtract the
quantity, there are many things that can go wrong with such an arrangement.
For example, say the user types 100 as the quantity, you subtract 100. Then
the user realizes they should have typed 10, so they go back and change it,
and your code now subtracts *another* 10. The quantity is now out by 100.
Similarly, if they realized the chose the wrong product, how will you
restore the quantity of the previous product? Or if they delete the row,
....? Or ...?

For an example of how to approach this, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html