Update query with SQL

[blueraincoat]

Hi Guys, I have a update query in command click event, and the SQL was like below, but system give me a warning that missing the end of the code(Sorry for my poor english),could somebody help find what's the problem?
Thanks in advance.



UPDATE tblInventoryInfo SET tblInventoryInfo.StockLevel= tblInventoryInfo.StockLevel-Me.Qty.Value _
Where(((tblInventoryInfo.PartNumber)="Me.PartNumbe r") AND ((tblInventoryInfo.LotNumber)="Me.LotNumber")));

[jzwp11]

You have to construct the query in a string variable and populate it with the values from the form controls you reference.

The following code assumes that the part number and lot number fields in the table are numeric datatypes

Dim mySQL As String
mySQL = "UPDATE tblInventoryInfo SET tblInventoryInfo.StockLevel= tblInventoryInfo.StockLevel-" & Me.Qty
mySQL = mySQL & " Where tblInventoryInfo.PartNumber=" & Me.PartNumber & " AND tblInventoryInfo.LotNumber=" & Me.LotNumber
 
CurrentDb.Execute mySQL, dbFailOnError

If the part and lot number fields are text fields, the code would look like this:

Dim mySQL As String
mySQL = "UPDATE tblInventoryInfo SET tblInventoryInfo.StockLevel= tblInventoryInfo.StockLevel-" & Me.Qty
mySQL = mySQL & " Where tblInventoryInfo.PartNumber='" & Me.PartNumber & "' AND tblInventoryInfo.LotNumber='" & Me.LotNumber & "'"
 
CurrentDb.Execute mySQL, dbFailOnError

[blueraincoat]

Thank you jzwp11.
But when I ran the code you give in my db, it gave an error 3061 too few parameters, what should I do ...

[blueraincoat]

I search the Internet and reviewed my db.
I have two tabels on is tblInventoryInfo, the other is tblPartsOutbound.
The main form is based on tblPartsOutbound, my purpose is when I take parts out of inventory, I'll keep the record in tblPartOutbound and update the stocklevel of the parts in inventory.
Could this be done?

[blueraincoat]

I'm awfully sorry,jzwp11
Your code works perfec without any question. I changed the name of one of my fields.
Thank you very much.
I'm going to mark this thread as solved.

[jzwp11]

Actually the better way to handle inventory is with 1 table where you record additions to as well as subtractions from inventory. You might take a look at this site from Allen Browne where he discusses inventory control

[blueraincoat]

Thank you for the suggestion.

[jzwp11]

You're welcome; good luck with your project.