Syntax Error on UPDATE statment

  • Thread starter Thread starter TitaniaTiO2
  • Start date Start date
T

TitaniaTiO2

All,

I am trying to use the following statement:

Dim sql As String
Dim sql2 As String
sql = "UPDATE tblPerson " & _
"SET LastName = '" & Me!txtLastName & "', " & _
"FirstName = '" & Me!txtFirstName & "', " & _
"JobTitle = '" & Me!txtJobTitle & "', " & _
"EmploymentStatus = '" & Me!txtNewEmploymentStatus & "', " & _
"WHERE PersonID = " & Me!txtPersonID
Debug.Print sql

CurrentDb.Execute sql, dbFailOnError

sql2 = "UPDATE tblEmployment " & _
"SET DepartmentID = '" & Me!txtDepartmentID & "', " & _
"WHERE PersonID = " & Me!txtPersonID


CurrentDb.Execute sql2, dbFailOnError
DoCmd.Close


I am getting an error "Syntax Error in UPDATE Statement"
It points to the 1st update statement. I am not sure what I am missing.
When I do the debug everything seems to look ok.

tblPerson
PersonID (Key - Autonumber)
LastName (Text)
FirstName (Text)
JobTitle (Text)
EmploymentStatus (text limited to "Existing Employee" Or "Obsolete
Employee")


I am sure it is something small and stupid.
txtNewEmploymentStatus is being updated based on a combo box that limits the
values to match those required in the validation rule.

Thanks!
Titania
 
TitaniaTiO2 said:
All,

I am trying to use the following statement:

Dim sql As String
Dim sql2 As String
sql = "UPDATE tblPerson " & _
"SET LastName = '" & Me!txtLastName & "', " & _
"FirstName = '" & Me!txtFirstName & "', " & _
"JobTitle = '" & Me!txtJobTitle & "', " & _
"EmploymentStatus = '" & Me!txtNewEmploymentStatus & "', " & _
"WHERE PersonID = " & Me!txtPersonID
Debug.Print sql

CurrentDb.Execute sql, dbFailOnError

sql2 = "UPDATE tblEmployment " & _
"SET DepartmentID = '" & Me!txtDepartmentID & "', " & _
"WHERE PersonID = " & Me!txtPersonID


CurrentDb.Execute sql2, dbFailOnError
DoCmd.Close


I am getting an error "Syntax Error in UPDATE Statement"
It points to the 1st update statement. I am not sure what I am missing.
When I do the debug everything seems to look ok.

tblPerson
PersonID (Key - Autonumber)
LastName (Text)
FirstName (Text)
JobTitle (Text)
EmploymentStatus (text limited to "Existing Employee" Or "Obsolete
Employee")


I am sure it is something small and stupid.
txtNewEmploymentStatus is being updated based on a combo box that limits
the
values to match those required in the validation rule.
Click to expand...


It's these lines:
"EmploymentStatus = '" & Me!txtNewEmploymentStatus & "', " & _
"WHERE PersonID = " & Me!txtPersonID
Click to expand...

The trailing comma after "EmploymentStatus = <whatever>" implies that
there's another field to be set, but you go right into the WHERE clause.
Try this:

"EmploymentStatus = '" & Me!txtNewEmploymentStatus & "' " & _
"WHERE PersonID = " & Me!txtPersonID

You'll need to make a similar fix to the second SQL statement.
 
Thanks!

I knew it would be something small

Dirk Goldgar said:
It's these lines:


The trailing comma after "EmploymentStatus = <whatever>" implies that
there's another field to be set, but you go right into the WHERE clause.
Try this:

"EmploymentStatus = '" & Me!txtNewEmploymentStatus & "' " & _
"WHERE PersonID = " & Me!txtPersonID

You'll need to make a similar fix to the second SQL statement.


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top