How to update a table from a form that is based on different table

[bluesh4rk]

I am using MS Access 2007.

I am working on a small database that does inventory management and
maintains data on orders. I want to be able to create orders and have the
order processed to update the inventory as well.

I already have a form designed to create a new order. It automatically
updates an Order_Master and Order_Detail table. The Order_Detail table
contains the data I need to update the Inventory table with. The Create
Order form is a form with Order_Master information and then has an imbedded
subform with Order_Detail information.

The order contains 3 of Widget-A and 1 of Widget-B. The Order_Detail &
Order_Master tables are updated automatically (Order_Detail is the table that
has the actual record that says 3 of Widget-A were ordered, etc). The
Inventory table which keeps track of how many Widgets are in stock also needs
to be updated to reflect the reduction of 3 Widget-As and 1 Widget-B. I am
guessing I need to create an update query and attach it to a "PROCESS
INVENTORY" button on the Create Order form to do this for me. I'm just not
sure how to actually do it or even if this is the best way to do it. I am
also concerned about a user accidentally hitting this button more than once
and then artifically reducing the inventory when it shouldn't be reduced. Is
there a way to prevent that?

Additionally, I want the user to be able to modify orders if necessary and
I'm not sure how to approach that as I'd need to modify the inventory table
again to reflect the changes.

Would it make sense just to create a field "PROCESSED" as yes/no or
something and set up a form where the user sees all the unprocessed orders
(as far as haven't been added to inventory yet) and have them check the
process box so the update query could go in and make the modifications
necessary? If this would work for modified orders.

Any pointers or help would be really appreciated. I guess my main problem
is simply not knowing how to update a table from a form that is based on
different tables.

Thanks

 

[John Smith]

The reason that this is difficult is that your database structure is wrong.
You are trying to store the order data twice, once as an order and once as a
reduction in the inventory, so your data is not properly normalised.

Rather than an inventory table create a delivery or production table
(depending on how you get your widgets). Into this you insert a record every
time that more widgets arrive. Your Inventory will then be a query that
subtracts the total order from the total delivery for each item. This will
always give you the correct answer regardless of how often the orders get changed.

HTH
John
##################################
Don't Print - Save trees