Syntax error (missing operator) in query expression - Run-time error 3075

[Kobus.Worx]

This is my VBA code:

CurrentDb.Execute "UPDATE tbl_01_00_Suppliers " & _
"SET fld_01_00_01_Supp_Cd='" & Me.txt_D_SupplierCode & _
", fld_01_00_02_Supp_Name='" & Me.txt_D_SupplierName & "'" & _
", fld_01_00_03_Supp_Loc='" & Me.cbx_D_SupplierLocation & "'" & _
", fld_00_01_04_Supp_Tel='" & Me.txt_D_SupplierTel & "'" & _
", fld_00_01_05_Supp_Fax='" & Me.txt_D_SupplierFax & "'" & _
", fld_00_01_06_Supp_Email='" & Me.txt_D_SupplierEmail & "'" & _
" WHERE fld_01_00_01_Supp_Cd=" & Me.txt_D_SupplierCode.Tag

This is the error when attempting to execute:

Syntax error (missing operator)in query expression "THHOWN099, fld_01_00_02_Supp_Name='The Helpful Handyman', fld_01_00_03_Supp_Loc etc.

I suspect the fld_01_00_01_Supp_Cd after SET

Any help will be highly appreciated - thank you

 

[isladogs]

The second line is the culprit. It should be

  "SET fld_01_00_01_Supp_Cd='" & Me.txt_D_SupplierCode & "" & _

The WHERE clause looks wrong as well. I think the '.Tag' part shouldn't be there.
Also if the Supp_CD field is already equal to the Supplier_Code control its not needed in the set clause...

CurrentDb.Execute "UPDATE tbl_01_00_Suppliers " & _
               "SET fld_01_00_02_Supp_Name='" & Me.txt_D_SupplierName & "'" & _
               ", fld_01_00_03_Supp_Loc='" & Me.cbx_D_SupplierLocation & "'" & _
               ", fld_00_01_04_Supp_Tel='" & Me.txt_D_SupplierTel & "'" & _
               ", fld_00_01_05_Supp_Fax='" & Me.txt_D_SupplierFax & "'" & _
               ", fld_00_01_06_Supp_Email='" & Me.txt_D_SupplierEmail & "'" & _
               " WHERE fld_01_00_01_Supp_Cd=" & Me.txt_D_SupplierCode;

I would strongly recommend simplifying your field and control names.
Suggest you remove repeated underscores and I would scrap fld prefixes.
Here's how I would do it just using camel case

CurrentDb.Execute "UPDATE tblSuppliers " & _
               "SET SuppName='" & Me.txtSupplierName & "'" & _
               ", SuppLoc='" & Me.cbxSupplierLocation & "'" & _
               ", SuppTel='" & Me.txtSupplierTel & "'" & _
               ", SuppFax='" & Me.txtSupplierFax & "'" & _
               ", SuppEmail='" & Me.txtSupplierEmail & "'" & _
               " WHERE SuppCd=" & Me.txtSupplierCode;

 

[June7]

Missing apostrophe:

"SET fld_01_00_01_Supp_Cd='" & Me.txt_D_SupplierCode & "'" & _

but already determined that line is not needed.

Still need apostrophes in WHERE clause, assuming SuppCd is a text field.

" WHERE fld_01_00_01_Supp_Cd='" & Me.txt_D_SupplierCode & "'"

 

[Kobus.Worx]

Thank you very much, I will work with your valuable advise this evening. I do appreciate

 

[Kobus.Worx]

Much appreciated, thank you for the advise and help. Will try it this evening

 

[Mark_]

For future help, I normally declare a STRING that holds my SQL values. This means I can check the contents of the string PRIOR to using it.

You could either debug.print the SQL string OR use a msgbox to show what you will pass. Makes it MUCH easier to debug these kinds of issues when you can see the result prior to using it.