Updating an Inventory Table using a form linked to another table

[Philywilly03]

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...)

Private Sub Command1_Click()
 
Dim strSQL As String
 
strSQL = "UPDATE Tbl.Inventory SET Inventory.On_Hand = " & Inventory.On_Hand - Me.Amount_Distributed & " WHERE me.Lot_Number = Inventory.Lot_Number "
 
End Sub

Am I in over my head?

[Seth Schrock]

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:

strSQL = "UPDATE Inventory SET Inventory.On_Hand = Inventory.On_Hand - " & Me.Amount_Distributed & _
               " 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:

Dim db As DAO.Database
Set db = CurrentDb
 
db.Execute strSQL, dbFailOnError

Make sure that line 4 is below your strSQL lines. So, something like this all together:

Private Sub Command1_Click()
 
Dim strSQL As String
Dim db As DAO.Database
 
Set db = CurrentDb
 
strSQL = "UPDATE Inventory SET Inventory.On_Hand = Inventory.On_Hand - " & Me.Amount_Distributed & _
         " WHERE Inventory.Lot_Number = " & Me.Lot_Number
 
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!

[NeoPa]

PhilyWilly03:
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 :

Private Sub Command1_Click()
    Dim strSQL As String
    Dim dbVar As DAO.Database
 
    On Error GoTo ErrorHandler
    strSQL = "UPDATE [tblInventory]" _
           & " SET [On_Hand]=[On_Hand]-" & Me.Amount_Distributed _
           & " WHERE [Lot_Number]=" & Me.Lot_Number
    Set dbVar = CurrentDb()
    Call dbVar.Execute(strSQL, dbFailOnError)
    Exit Sub
 
ErrorHandler:
    'Whatever
End Sub

NB. The code to create strSQL is largely guesswork on my part but probably illustrates where you need to make changes.