By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,819 Members | 1,459 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,819 IT Pros & Developers. It's quick & easy.

Updating an Inventory Table using a form linked to another table

P: 1
I am attempting to update a table that keeps an inventory using a form that creates records for for a different table. I'm pretty weak on the programming side, but I have been looking around at other posts and thought I came up with what would work. My goal is when pressing a button, the number entered into the "Amount Distributed" box on my form (that is linked to a distribution table) will be subtracted from the "On Hand" column on my Inventory table for a specific lot number. I have a combo box on my form that selects the lot number from a query that references my Inventory table.
Here is what I have (that does not work...)
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command1_Click()
  3. Dim strSQL As String
  5. strSQL = "UPDATE Tbl.Inventory SET Inventory.On_Hand = " & Inventory.On_Hand - Me.Amount_Distributed & " WHERE me.Lot_Number = Inventory.Lot_Number "
  7. End Sub
Am I in over my head?
3 Weeks Ago #1
Share this Question
Share on Google+
2 Replies

Seth Schrock
Expert 2.5K+
P: 2,941
First, lets work on the strSQL variable. I'm guessing based on what I see that you have a table named Inventory that has at least a On_Hand field and a Lot_Number. If this is all correct (exact names are important), then lets continue with the UPDATE query code. Immediately following the UPDATE keyword comes the table name. You have Tbl.Inventory. Again assuming what I said above is true, then this should just be Inventory. Then comes the SET keyword. You are setting Inventory.On_Hand to equal an amount. Because you have ended the quotation, the VBA code is going to try to calculate the Inventory.On_Hand - Me.Amount_Distributed before putting the difference into the SQL string that you are building. Unfortunately, VBA won't know what Inventory.On_Hand means, only the SQL code will when it runs. So you need the Inventory.On_Hand - all inside the quotes and then the Me.Amount_Distributed outside the quotes. In the WHERE clause, you have the order of the information reversed and don't have the quotes correct. All of that said, here is the what (I think) the strSQL variable should look like:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "UPDATE Inventory SET Inventory.On_Hand = Inventory.On_Hand - " & Me.Amount_Distributed & _
  2.                " WHERE Inventory.Lot_Number = " & Me.Lot_Number
Now, all this code has done is assign some text to a string variable. Access doesn't even know that this is a query that needs to be ran. It is just some text just like these posts are just text. We have to tell VBA to run it as a query. Add the following:

Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Set db = CurrentDb
  4. db.Execute strSQL, dbFailOnError
Make sure that line 4 is below your strSQL lines. So, something like this all together:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command1_Click()
  3. Dim strSQL As String
  4. Dim db As DAO.Database
  6. Set db = CurrentDb
  8. strSQL = "UPDATE Inventory SET Inventory.On_Hand = Inventory.On_Hand - " & Me.Amount_Distributed & _
  9.          " WHERE Inventory.Lot_Number = " & Me.Lot_Number
  11. db.Execute strSQL, dbFailOnError
Now as far as inventory databases go, they are very tricky and require a good amount of knowledge. I'm going to guess that you may be over your head right now, but by no means give up. You should become very fluent with SQL queries. Also, Database Normalization is a very important concept that you should become familiar with and utilize. Unfortunately I don't have a link for proper table design for an inventory database, but basically, you shouldn't be storing an On Hand amount and changing it every time you distribute one. Instead, you should have a transactions table and then use a query to make the calculation for you for a real-time On Hand amount.

Hope this helps. You should also read Before Posting Code. It gives some site rules as well as good troubleshooting techniques that you can use when trying to figure out what the problem is.

Welcome to Bytes!
3 Weeks Ago #2

Expert Mod 15k+
P: 31,470
Am I in over my head?
If I'm honest it does look very much like it.

Without a view of the whole system it's hard to advise too reliably but applying updates using SQL as you're looking to do is rarely appropriate. Not never, but rare. Let's skip that for now though. The scope of that would require more attention than you should expect from a forum site. You should be looking at education or employing an expert. Neither trivial.

For this question you're looking at creating SQL on the fly using VBA and then executing it from VBA. Check out How to Debug SQL String. It looks like you're very likely to produce SQL that is not what you require. The linked article will hopefully help you to understand the separation between the VBA side of things and the resultant SQL. Your code indicates that may be confusing you a little.

When you have executable SQL that can work you then have to execute it. This is currently missing from your code. You'd need to add this as my example below :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command1_Click()
  2.     Dim strSQL As String
  3.     Dim dbVar As DAO.Database
  5.     On Error GoTo ErrorHandler
  6.     strSQL = "UPDATE [tblInventory]" _
  7.            & " SET [On_Hand]=[On_Hand]-" & Me.Amount_Distributed _
  8.            & " WHERE [Lot_Number]=" & Me.Lot_Number
  9.     Set dbVar = CurrentDb()
  10.     Call dbVar.Execute(strSQL, dbFailOnError)
  11.     Exit Sub
  13. ErrorHandler:
  14.     'Whatever
  15. End Sub
NB. The code to create strSQL is largely guesswork on my part but probably illustrates where you need to make changes.
3 Weeks Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.