Q: I am building an inventory database for my business. I am having trouble autoupdating stock level. I create sales orders through a form and that is recorded within a Table - inventory transactions. On this form there is a box to select the type of products and also a number box to input the number of units the customer purchases. I would select product A, enter the amount, that amount will then be automatically deducted from InStock for that particular product. I am stumped about what code to add to my database. Any help will be appreciated!


A: Something like this would be best handled by an UPDATE QUERY or a RECORDSET. I have a tutorial on creating update queries:

/tips/access/update-query

You could run that at the end of the day to update all of your stock inventory with the days sales.

If you wanted to do it in REALTIME, you should use a recordset and adjust each item as it goes into an order.

I don't have a tutorial on recordsets YET, but I will be making one in the next week or two. Here's the basic jist:

set db = currentdb()
set rs = db.openrecordset("SELECT * FROM MyProductTable WHERE ProductID=" & CurrentProductID,dbOpenTable)
rs.edit
rs!quantityOnHand = rs!quantityOnHand - AmountSold
rs.update
rs.close
set rs = nothing
set db = nothing

That's the basics of a recordset. There's a LOT more to it, but that should get you started. Again, I plan on fully covering this in an upcoming tutorial.