Update Inventory [In Stock] Amount (Access 2003/SP

How can I update the 'In Stock' amount in a 'Products' table when I am entering orders. For example, 'Products' could have 50 apples 'in stock'. I enter an order of 10 apples on an 'Orders' form and the 'in stock' would automatically be updated to 40. Is this done through an update query?

Re: Update Inventory [In Stock] Amount (Access 2003/SP

You can either run an update query or use DAO or ADO code to open a recordset on the Products table, locate the relevant record and edit it.

You must be very careful how you do this. For example, you don't want to update the Products table immediately when the user enters a number in the Orders form - the user might have made a mistake, and have to change the number. You should also provide a mechanism for changing the size of the order after it has been confirmed - the stock should then only be changed by the difference between the old and new order size.

If I were to design an inventory database, I'd use unbound forms to have complete control over the way data are stored.

Re: Update Inventory [In Stock] Amount (Access 200

I'm 'trying' to design a database for a charity group that works with used and new clothing. It needs to be something very simple, the users aren't highly computer literate. "I don't seem to be either". That's one thing they want is an updated Inventory status. I understand the need to be cautious about changing the order. Should I run an update query based on order numbers at the end of the day? ADO and DAO are not my specialty, although if I had help I'd be willing to give it a try.

Re: Update Inventory [In Stock] Amount (Access 2003/SP

You might want to reconsider trying to store the current inventory level. It is frought with potential problems; that is, there is always uncertainity as to whether you have properly updated the level in every situation that might occur. Usually a better solution is to derive the current inventory level by using queries of inventory ins and outs.