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

Kobus.Worx

New member
Local time
Today, 18:49
Joined
May 6, 2019
Messages
4
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

MVP / VIP
Local time
Today, 16:49
Joined
Jan 14, 2017
Messages
18,186
The second line is the culprit. It should be

Code:
  "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...

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

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

AWF VIP
Local time
Today, 08:49
Joined
Mar 9, 2014
Messages
5,423
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 & "'"
 
Last edited:

Kobus.Worx

New member
Local time
Today, 18:49
Joined
May 6, 2019
Messages
4
Thank you very much, I will work with your valuable advise this evening. I do appreciate
 

Kobus.Worx

New member
Local time
Today, 18:49
Joined
May 6, 2019
Messages
4
Much appreciated, thank you for the advise and help. Will try it this evening
 

Mark_

Longboard on the internet
Local time
Today, 09:49
Joined
Sep 12, 2017
Messages
2,111
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.
 

Users who are viewing this thread

Top Bottom