How do you automatic update a table in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to get my order table Quatity -1 field to automatically subtract
from the Inventory table Quantity field in Mirosoft Access 2003. How do I do
this?
 
Andre, it is not quite as simple as that.

You can use thethe AfterUpdate event of the *form* (not control) to execute
an Update query statement to subtract the quantity from the right record in
the table. Before you do that, consider these scenarios:

After saving the record, the user realizes the Quantity should have been
100, not 1000. They delete the last 0, and save the change. Does your code
now subtract *another* hundred, as well as the thousand that was already
subtracted?

Or they change the product and save. Do you restore the product that was
subtracted from the wrong product, as well as subtracting from the right
product now?

And what happens if they delete the row from the order? Do you restore the
value?

What about if they select several rows at once to delete? Do you restore all
the values?

Even if you can sort all that out, if you are just subtracting quantities,
how can you demonstrate that the value you have not is correct?

I suggest you get Access to calculate the quantity available at the time you
need it, instead of storing this value. For an introductory explanation of
how to do that, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html
 
Back
Top