How to Add a Rec in a table / Update a field in another using a calc

[LadyScot101]

I am creating an Inventory DB. I have a Table_Parts that lists all the parts that is in Inventory. Then I have a form that updates Table_Inv Control that shows what a person has checked out of Inventory. Now the problem is, I can't get the Quantity from the Table_Parts to go down using the Qty field in the Form that updates the Table_Inv Control. Say a person checks out 1 Hammer. The Table_Inv Control will show 1 Hammer listed while Table_Parts will show that the Quantity has gone down by 1. Any help on setting this up? I tried using an expression and that didn't work. I've tried using a "On Change" and that didn't work. I don't know how to use a query in this setup because the Table_Inv Control's Quantity isn't supposed to be changed, only the Table_Parts. Any ideas?

 

[gwinn7]

Why store the quantity as a persistent value? Since this value is dependent on outside conditions, then, why not have a query do the work for that calculation on the fly?

If you still want to persist this quantity, then I could recommend updating it everytime one of your forms load, but that could be problematic when it involves a LOT of records. One other option is to update the quantity through an update query based on the current inventory item viewed (much faster). Use the Form_Current event for that. That could be your answer.

A trigger would suit you best for what you are intending to accomplish, if the query suggestion is not your option. But triggers are not supported by Access. Therefore, I strongly recommend ditching Access as a backend and pickup PostgreSQL which is very easy, with lots of power and capability; free to use! You can still use Access as a front-end, if you want to.

This comes with years of experience using/programming Access and being sick of its limitations and quirks.

Gary
gwinn7

 

[LadyScot101]

I checked with my IT dept and they won't let me add the additional software to work with Access, so I'm stuck with the Access limitations. I have to have a permanent file of what the individual checked out of inventory. Mgt runs reports off of it, but at the same time it's got to subtract from the main inventory parts table. I tried an update query using "On Click" and also "On Change", but neither one worked. I might have been creating the update query incorrectly. Don't know.