Brian,
Make a new query on the Product table, and while in the design view, change
it to an Update query (Query > Update query).
In the criteria line below field ProdID enter a criterion like"
Forms![Order Entry Form Name]![ProdID Control Name] (change names
accordingly)
In the Update to like below the QuantityInStock field, enter an expression
like:
[QuantityInStock] - Forms![Order Entry Form Name]![Quantity Control
Name] (change names accordingly)
Save the query.
Now, make a macro as follows:
action: SetWarnings argument: false
action: OpenQuery argument: query name (the one above), rets arguments
default
action: SetWarnings argument: true
Save the macro
The last thing to do is determine the event to run the query. You could use
the Before Update or On Change Event of the Quantity control on the box, but
this will run the action query and subtract stock with EVERY change, so if
the user makes a typing error and then corrects, both will be subtracted!
Therefore I would suggest a separate command button to run the macro
(confirmation that the quantity is correct / final).
This is a workable solution along the lines of what you asked, just because
you mentioned "macro". This has a major disadvantage: press "save" twice and
you deduct the stock on hand twice!
Personally, I would try to add some more functionality to "know" if order is
already saved, so if there is a change (second save) add the old value and
then subtract the new one etc. Regrettably, I don't know if I could do this
just with plain macros; I would definitely go the VB code way.
HTH,
Nikos
