Forum Discussion
Subform saving records before I actually click save
I'm sure this has been answered before but I am trying to create a simple inventory system and I have an intersection table between Product and Supply Order because the relationship is many-to-many. I have a form for the user to enter the supply invoice number as well as all the products that arrived in this order (see attached). I want the quantity of the product we have in stock to increase when the form is saved. For ex: we have 65 pieces of part 'abc' and 10 more arrive so I want the quantity of 'abc' in the Product table to be 75. I'm encountering a problem because it is creating the records before I even click the save button. The 'Product-Supply Order' records are added when I move to the next line. Even if I close out of the form, those records remain. So my 'AfterInsert' macro for that table is triggered after every line instead of once at the end after hitting save. Any way to fix this? Or is there a much easier way to do this? Got to be an easier way to update the quantity after the records are created. Sorry....very new at this.
There are two different problems here. Let's address the table design problem first.
In a relational database application we do NOT store calculated values like inventory balance. It's a design choice that seems okay until you try to use it in practice. Don't do it, in other words. Remove that field from the table(s) and delete the table macros trying to update it.
That will actually help address the other issue. I don't call it a problem, per se, because it is just the way Access forms, or I should say bound Access forms, are supposed to work. Changing focus in the form from one record to another automatically saves the record losing focus. So your "Save" button turns out to be redundant.
How do you create a report showing inventory balances, you ask? You do that in an aggregate query which sums up all of the inventory received, all of the inventory used to fill orders, and computes the balance between those two totals.
I suggest you look into the Northwind sample database to get some better insight into valid table design approaches.
