Syntax Error (Missing Operator) in Update SQL statement for VBA Access

Question

I have a form that references a linked table (called "Customer Information") to auto-fill information (such as street address, phone number, etc.) about a customer once the user has entered the customer's name into the form (called "Quote Form"). Once the information has been auto-filled, the user still has the opportunity to change those fields. However, if they change those fields such that they no longer match the information on record for that particular customer, a message box asks them if they'd like to update the customer's information. If they click yes, I want a SQL statement to update the information in the "Customer Information" table to match the information on the "Quote Form".

Unfortunately, I am receiving the following error: "Run-time error '3075': Syntax error (missing operator) in query expression 'insert result of "Form.Controls(Box)" -- see code below'.

I am using a public function because I plan to repeat this procedure for several different fields. Here is my code:

Private Sub StreetAddressEntry_LostFocus()
'Check for match with existing customer information and update if necessary
    Call UpdateCustomerInfo("StreetAddressEntry", "[Street Address (Billing)]", "[Customer Information]", "[Customer Name]", "CustomerEntry", "street address")
End Sub

Public Sub UpdateCustomerInfo(Box As String, Fld As String, Tbl As String, lookupFld As String, lookupBox As String, topic As String)
'Underlying function for updating customer information
Dim MsgBoxAnswer As Variant
If Form.Controls(Box).Value <> DLookup(Fld, Tbl, lookupFld & " = " & lookupBox & ".Value") Then
    'Request Permission to update customer information based on conflicting entries
    MsgBoxAnswer = MsgBox("The " & topic & " does not match what is currently listed for this customer. Would you like to update the customer's information to match?", vbYesNo, "Edit Customer Information?")
    If MsgBoxAnswer = vbNo Then 'No permission granted to add a new customer to the list.
        Exit Sub
    Else 'Permission granted to add new customer to the list.
        DoCmd.RunSQL "UPDATE " & Tbl & " SET " & Fld & " = " & Form.Controls(Box) & " WHERE " & lookupFld & " = " & Form.Controls(lookupBox)
    End If
End If
End Sub

I would appreciate any suggestions or tips on how to improve this code and resolve the error message.

K4M K4M · Accepted Answer · 2020-07-30T22:31:17

To debug: you can your SQL text into a variable before running and then print using MsgBox to see what is the SQL text generated. That usually helps catching the problem. If still not clear, you can run that SQL inside a Query in Access and see if it works there.

My guess is that you're missing single quotes if the columns you're trying to update are string columns. Try this

mySQL = "UPDATE " & Tbl & " SET " & Fld & " = '" & Form.Controls(Box) & "' WHERE " & lookupFld & " = '" & Form.Controls(lookupBox) & "'"

MsgBox (mySQL)

DoCmd.RunSQL

Also if table name or column names includes non-alphanumeric characters (like space etc.), you need to use brackets around the table/column names but I think this is not the case with yours as I see square bracketed columns in your code.

Again, the best is to print the SQL text generated and run it inside your database client (in this case Access Query tool)

Syntax Error (Missing Operator) in Update SQL statement for VBA Access

1 Answers