Updating underlying tables

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

Guest

I have a form for order entry (based on NW MDB). It allows for "Quantity" of
each item of the order. I want to update the balance in the underlying
product table to reflect the "reduction" of "UnitsInStock" based on the order
entry.

Currently, the "Products" table is pulled into the equation by a query. I
assume that I can enter a "formula" in the query that will perform the above
function but have no clue what that formula would look like except that the
results should basically be: "UnitsInStock = [UnitsInStock]-[Quantity]"
 
Bill, it's not quite as simple as that.

What will you do it the quantity is changed from 1000 to 100 in an existing
order? Will you put 900 back in?

What about if the product is changed in an existing order? Will you put the
1000 or the original product back, and take out 1000 or the new product as
well?

What if the row of an order is deleted? Will you put the quantity back in
again there too? And how will you achieve that if the user selects 2 or more
rows at once in continuous form/datasheet and deletes them? And what if the
user cancels the confirmation dialog so the delete does not proceed?

The best choice might be to calculate the quantity available at the time you
need it, rather than try to store the quantity in the table. For an
introduction on how to do that, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html

If you want to do more than that (such as keeping track of which specific
stock was assigned to what order on what date), buy "Building MS Access
Applications" by John Viescas (Microsoft Press, 2005.) The book comes with a
sample database for inventory management. The application is worth much more
than the book, and the book is a very good reference manual for the
application.
 
Thank you.
The issues you address are real and quite possible. Hadn't thought of them
yet. It would have reared it's ugly head soon though.

I will approach from a different angle.



Allen Browne said:
Bill, it's not quite as simple as that.

What will you do it the quantity is changed from 1000 to 100 in an existing
order? Will you put 900 back in?

What about if the product is changed in an existing order? Will you put the
1000 or the original product back, and take out 1000 or the new product as
well?

What if the row of an order is deleted? Will you put the quantity back in
again there too? And how will you achieve that if the user selects 2 or more
rows at once in continuous form/datasheet and deletes them? And what if the
user cancels the confirmation dialog so the delete does not proceed?

The best choice might be to calculate the quantity available at the time you
need it, rather than try to store the quantity in the table. For an
introduction on how to do that, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html

If you want to do more than that (such as keeping track of which specific
stock was assigned to what order on what date), buy "Building MS Access
Applications" by John Viescas (Microsoft Press, 2005.) The book comes with a
sample database for inventory management. The application is worth much more
than the book, and the book is a very good reference manual for the
application.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bill said:
I have a form for order entry (based on NW MDB). It allows for "Quantity"
of
each item of the order. I want to update the balance in the underlying
product table to reflect the "reduction" of "UnitsInStock" based on the
order
entry.

Currently, the "Products" table is pulled into the equation by a query. I
assume that I can enter a "formula" in the query that will perform the
above
function but have no clue what that formula would look like except that
the
results should basically be: "UnitsInStock = [UnitsInStock]-[Quantity]"
Click to expand...
Click to expand...
 
Back
Top