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
braX
  • 11,506
  • 5
  • 20
  • 33
Bryan Clinkenbeard
  • 23
  • 5
  • 1
    show table VehicleInfo structure(fields). Do you try updating more than one row? Add a link to yesterdays question. [Debug](https://stackoverflow.com/a/1099570/9439330) your query. – ComputerVersteher Jul 03 '19 at 17:31
  • 1
    Number counting in column names (Vic**1**License, ..., Vic**6**License) 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. – ComputerVersteher Jul 03 '19 at 20:43

2 Answers2

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.

Lee Mac
  • 15,615
  • 6
  • 32
  • 80
  • All my fields are string. Thank you for catching the comma. I removed the comma but I still get the same error message. – Bryan Clinkenbeard Jul 03 '19 at 17:24
  • 1
    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.. – Jon Jul 03 '19 at 17:41
  • yes, I use a msgbox to review the command. All variables are being populated correctly – Bryan Clinkenbeard Jul 03 '19 at 17:43
  • 1
    @BryanClinkenbeard Can you screenshot or show what the actual SQL statement is? Did you check for extra apostrophes? – Jon Jul 03 '19 at 19:32
-1

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

Bryan Clinkenbeard
  • 23
  • 5