Updating Inventory On The Fly

[Guest]

I have a form that uses a combo box to look up Parts. The user enters the
quantity of the part to order and the OnHand number shows up. Now, what I
need to do is add a button to "Update Inventory" that will commit the
Quantity, go back out to the Parts file, and subtract the Part order from
OnHand and refresh the form with the updated OnHand quantity.

Example: The Form pulls in the Parts file which hods all the pertinent data
such as Description, Price, Cost, OnHand, ReorderPoint, ReorderQty, etc. I
read in the Parts data and load this via the combo box. Now, how do I update
the Qty OnHand on the fly?

I thought of a Macro, but couldn't get it to work correctly. I think I'm
close, but need a bit of help on this one.

Thanks in advance...MDM

 

[Steve Schapel]

MDM,

Whenever you come to a situation where you want to save a calculated
value to a table, stop. It's probably not a good idea. In this case, I
would suggest that you do not want an OnHand field at all, and the
simplest answer to your question is to remove this field from the table.

Mind you, you are trying to design and build a stock management system,
which is not exactly a trivial exercise. But normally, if you are
recording movements of the product out, and movements of the product in,
then the "onhand" quantity will be derivable, whenever you need it, via
a simple Totals Query.

It will be worthwhile to have a look at Allen Browne's article on this
subject: http://www.allenbrowne.com/AppInventory.html

 

[Guest]

Steve

Thanks very much. I think I'll create an inventory transactions table and do
as you advise, calculate the OH instead of storing it.

You're the best....MDM