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.

Upload Images   Link   Richard Rost  14 years ago Robert, I haven't covered recordsets AT ALL in either my Access, VB, or ASP lessons - although they can be used in all three. I have been getting a LOT of requests for this, so as soon as I finish some of the other classes that have already been promised to my customers, I will definitely be putting this lesson together! Sorry, only students may add comments. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.

Upload Images   Link   robert prideaux  14 years ago If I am correct (and there is no guarantee here!); These Recordsets are used in ecommerce (and other db driven) sites? It is just I am currently drowning in Dreamweaver (and some WA extensions) trying to develop a shopping cart for my web site - and the RS keeps popping up... Is this covered in any of the Access or ASP lessons? Sorry, only students may add comments. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.

Upload Images   Link   Alan Hill  14 years ago Now that's something I've been waiting for RECORDSETS. There are a few things I would like explained. I am looking forward to these classes. I have copied and pasted your example into my notes already. Read More... Regards Alan Hill Sorry, only students may add comments. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.

Show Older Comments...
View in Table Format