Insert Into?? Add record to an unbound table??

[JK]

I'm not sure how to do this. I'm using Access03; I have a workorders form and
then a workorder parts subform. The subform is bound to the workorder parts
table.

I also have a table called "InventoryTransactions." This is from one of the
MS example templates. If a user creates a PO, the quantity ordered and
quantity received values are added to the table InventoryTransactions.

When a technician removes a part from inventory, he/she must manually add
the quantity to InventoryTransactions.

However, I already have them creating a workorder and in the workorder they
enter the parts they used on the project. So, rather than having them create
a workorder and then go manually enter the parts in Inventory I'd like the
InventoryTransaction table to automatically update when a workorder is
completed and parts have been added. A new transaction record would have to
automatically created.

I hope that makes sense.

What would be the best way to do this? Insert Into?

Any advice would be greating appreciated.

Jason

 

[Allen Browne]

Jason, this may be more complex than you anticipated. Not only do you need
to remove an item when a new row is entered, but consider how you will
handle cases such as:
- Restore the item if the user deletes the row from the work order parts.
- Alter the quantity removed if the user changes an existing record.
- Restore the old part, and remove the new one if the user changes the part
number on an existing record.

In my view, it is better to get Access to calculate the quantity when needed
rather than store the quantity. Here's an example of how that's done:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html