0

I am new to both MS Access and SQL. Now I am trying to create an inventory database for our company in Ms Access. I try to extract data from the reception form to update the inventory balance. But I met a syntax error message when I executed a SQL update statement. This is weird for me because I used the same statements that successfully running in other tables. The only difference is my former successful update working by direct text replacement and my error occurring update is working in a numeric object.

Please help me to check where I am wrong.

This is my code:

Private Sub Command96_Click()
CurrentDb.Execute "UPDATE tbl_Current_Stock" & _
                  "SET Stock_Level= Stock_Level + " & Me!txtOrderQty & "" & _
                  "Where tbl_Current_Stock.Raw_Material= " & Me!cboPurchase.Column(1) & ""
 End Sub

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
jiangzhou He
  • 57
  • 1
  • 7
  • Are those underscores really in your code? Anyway, I don't see a space between the `UPDATE` and `SET` statements. – Tim Biegeleisen Oct 04 '16 at 14:46
  • 1
    Underscores in Visual Basic mean to continue the current line on the next line. – Robert Columbia Oct 04 '16 at 14:47
  • Please read: [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) and [How to debug VBA code](http://www.cpearson.com/excel/DebuggingVBA.aspx) – Andre Oct 04 '16 at 15:09

3 Answers3

3

You need to add spaces before SET and Where. Otherwise, your command will look something like UPDATE tbl_Current_stockSET Stock_Level= Stock_Level + 3Where.....

Private Sub Command96_Click()
CurrentDb.Execute "UPDATE tbl_Current_Stock" & " " & _
              "SET Stock_Level= Stock_Level + " & Me!txtOrderQty & " " & _
              "Where tbl_Current_Stock.Raw_Material= " & Me!cboPurchase.Column(1) & ""
End Sub

You might also need to wrap the Raw_Material column in quotes if it is not numeric.

Robert Columbia
  • 6,313
  • 15
  • 32
  • 40
  • Thank you Robert. In my case Raw_Material is a lookup column. By the way, I am writing the sql statement one clause one line. And I wrote the code in office word. How could you find a space is missing in my codes. Could you please recommend me some good sql code editor? Thanks! – jiangzhou He Oct 04 '16 at 17:00
2

check your sentence correctly. there is no technical error. there are some space missing in you query.

just add white space before "SET" and "where" words.

CurrentDb.Execute "UPDATE tbl_Current_Stock" & _
              " SET Stock_Level= Stock_Level + " & Me!txtOrderQty & "" & _
              " Where tbl_Current_Stock.Raw_Material= " &     Me!cboPurchase.Column(1) & ""
maulik kansara
  • 1,087
  • 6
  • 21
  • Just as you mentioned I put the statments withion on sentence and it works! Thank you very much for your correction. – jiangzhou He Oct 04 '16 at 17:00
1

Friend, follow some tips to generate your updade correctly:

  • Check the spaces after concatenating your query
  • Be careful not to generate queries with keywords stuck together

UPDATE tableTestSET nome = 'My Name' WHERE active IS NOT NULL (wrong)

UPDATE tableTest SET name = 'My Name' WHERE active IS NOT NULL
  • Do not forget to use quotation marks when using strings

UPDATE tableTest SET name = My Name WHERE active IS NOT NULL (wrong)

UPDATE tableTest SET name = 'My Name' WHERE active IS NOT NULL

I hope it helps...

Good Luck!

ℛɑƒæĿᴿᴹᴿ
  • 4,983
  • 4
  • 38
  • 58