Hi Frank,
Try the methods outlined in these two articles:
How to Automatically Subtract a Quantity Ordered from Your Inventory
http://support.microsoft.com/?id=252813
Inventory Control: Quantity on Hand
http://allenbrowne.com/AppInventory.html
Tom
____________________________________________
thanks for your generous reply, but alas, i'm not much of a vb programmer.
how about a DSum function. could i use that?
//frank
____________________________________________
Frank,
It can be done quite neatly with some VB code (not sure this complies with your definition
of "painless"...). First of all, I would suggest you place a button on your order entry
form to (a) save the current order, (b) read the order number (and possibly close the
form), and (c) run the inventory update code.
The code should:
1.Open your inventory table as a recordset
2.Run a select query on your order line item table to select the specific order's lines,
and open this as a second recordset
3.Use two nested loops: outer loop on the second recordset to read each line item's item
code and quantity, inner loop on the first recordset to find the item's record and
subtract the order quantity from the inventory.
A couple of points you might want to consider:
Make your form a data entry one, so the user cannot use it to modify an existing order, as
this would distort your stock situation.
Make the button close the form as suggested, so the user cannot run the code twice on the
same order upon entry.
Use a separate form for order change (copy of the first one, data entry > no, allow
additions > no). Apart from the code for updating the stock after the change, this one
should have some additional code to "return" the original order's stock before saving the
change. One way to secure this is to (a) have all controls enabled > no by default, and
(b) add a second button to run the code to enable the controls and add the original order
quantities back to the inventory (so the user has to return the stock before they can make
changes).
Also, I would suggest you use a select query as a recordsource for the order change form,
which asks the user to enter the order number they want to change. This will prevent the
user from going to Edit mode and then go on to another order and modify / save again
without returning the stock first (if you do this the allow additions > no setting is
redundant).
HTH,
Nikos
____________________________________________
hi all:
is there a painless way to subtract inventory from my running inventory total once a sale
is made?
i have 50 units the customer purchases 15 of these units, my inventory for that item
should now 35 units available.
thank you in advance for any help.
//frank
