0

I have the following code on a button click and it generates the 3144 Syntax Error in UPDATE statement. I can't seem to find the error causing factor.

strUpdate = "Update VehicleInfo SET " & _
"Vic1License ='" & strPlate1 & "', Vic1Make ='" & strMake1 & "', Vic1Model ='" & strModel1 & "', Vic1Year ='" & strYear1 & "', Vic1Color = '" & strColor1 & "'," & _
"Vic2License ='" & strPlate2 & "', Vic2Make ='" & strMake2 & "', Vic2Model ='" & strModel2 & "', Vic2Year ='" & strYear2 & "', Vic2Color = '" & strColor2 & "'," & _
"Vic3License ='" & strPlate3 & "', Vic3Make ='" & strMake3 & "', Vic3Model ='" & strModel3 & "', Vic3Year ='" & strYear3 & "', Vic3Color = '" & strColor3 & "'," & _
"Vic4License ='" & strPlate4 & "', Vic4Make ='" & strMake4 & "', Vic4Model ='" & strModel4 & "', Vic4Year ='" & strYear4 & "', Vic4Color = '" & strColor4 & "'," & _
"Vic5License ='" & strPlate5 & "', Vic5Make ='" & strMake5 & "', Vic5Model ='" & strModel5 & "', Vic5Year ='" & strYear5 & "', Vic5Color = '" & strColor5 & "'," & _
"Vic6License ='" & strPlate6 & "', Vic6Make ='" & strMake6 & "', Vic6Model ='" & strModel6 & "', Vic6Year ='" & strYear6 & "', Vic6Color = '" & strColor6 & "'," & _
" WHERE LastName= '" & strLast & "', AND FirstName= '" & strFirst & "'"

DoCmd.RunSQL strUpdate
Improve this question
2
  • 1
    show table VehicleInfo structure(fields). Do you try updating more than one row? Add a link to yesterdays question. Debug your query. Commented Jul 3, 2019 at 17:31
  • 1
    Number counting in column names (Vic1License, ..., Vic6License) is an indicator for wrong table design. You can store information for up to 6 vehicles per owner in your table? That has some disadvantages (empty fields, count vehicles, > 6 vehicles, ...). if you have a field for a number (vehicleId), you can store as many vehicles per owner as needed. The number 1 to 6 can be computed by counting the vehicles per owner with id is same or lower. Bad design like that causes lots of trouble later on and is hard to fix, when db grows. Thats why database normalization should be first step. Commented Jul 3, 2019 at 20:43

2 Answers 2

Reset to default
2

There shouldn't be a comma in the where clause:

" WHERE LastName= '" & strLast & "', AND FirstName= '" & strFirst & "'"
                                   ^--------------------------------------- Remove this

Nor following the last field updated:

Vic6Color = '" & strColor6 & "'," & 
                               ^------------------------------------------- Remove this

Also, are all of your fields (Vic#License, Vic#Make, Vic#Model, Vic#Year etc.) all string-valued fields? If not, you should also remove the single-quotes surrounding the values populating those fields which are not string-valued, e.g.:

Vic1Year ='" & strYear1 & "',

Becomes:

Vic1Year =" & strYear1 & ",

However, whilst the above addresses the syntax issues with your code, in general, I would strongly suggest avoiding building SQL queries using concatenated variable values for two reasons:

  • The values must be converted to strings representing the true field value.

  • If the user is supplying the variable values, you have no control over the content of the resulting SQL query, which may be subject to SQL injection.

Instead, I would suggest parameterising your query which avoids both of the above.

I also completely agree with the comments made by @ComputerVersteher: incrementally named fields are a clear indication of poor database design and lack of database normalisation.

Assuming that your database descibes vehicle ownership, I would personally suggest three tables:

Owners

+--------------+
| OwnerID (PK) |
| FirstName    |
| LastName     |
+--------------+

Vehicles

+----------------+
| VehicleID (PK) |
| Make           |
| Model          |
| Plate          |
| Year           |
| Color          |
+----------------+

Vehicle-Owners (Junction Table)

+----------------+
| ID (PK)        |
| OwnerID (FK)   |
| VehicleID (FK) |
+----------------+

Here, I have assigned a separate primary key field in the Vehicle-Owners junction table which allows for a vehicle to be owned by multiple owners simultaneously (if you want to permit such a scenario); otherwise, you could form the primary key from the combination of the OwnerID & VehicleID to enforce a single ownership of a vehicle.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

All my fields are string. Thank you for catching the comma. I removed the comma but I still get the same error message.
Are you able to see what strUpdate contains right before the RunSQL statement? Can you set a breakpoint and view the contents of strUpdate to see how it looks? Maybe your data is the problem..
yes, I use a msgbox to review the command. All variables are being populated correctly
@BryanClinkenbeard Can you screenshot or show what the actual SQL statement is? Did you check for extra apostrophes?
-1

I found my error. There is a comma after the last record strColor6 & "',"Once I removed it, the error went away.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.