Syntax error in Update Statement

[thr33xx]

I am running into the following error when attempting to execute an update on some fields. I receive the following error,

"Run-time error '-2147217900 (80040e14)':
Syntax error in UPDATE statement."

Here is the following code being executed. With the highlighted being the issue. I have also placed a message box before the code executes, and it is gathering the correct info to update. As far as I can tell, all syntax is correct... any ideas

Private Sub Add_New_Study_Click()
Dim sqlstr As String
Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection


'Updates/Save changes to study info
sqlstr = "Update dbo_Setup "
sqlstr = sqlstr & "Set Coor_ID_Last ='" & Me.Coor_ID_Last & "', where Study_id = '" & Me.Add_Study_ID & "' "
MsgBox sqlstr
[B][COLOR="Red"]cn.Execute sqlstr[/COLOR][/B]

cn.Close
Set cn = Nothing

'Call Add_Study_ID_AfterUpdate    'refresh
MsgBox "Save successful."

End Sub

 

[boblarson]

Are Coor_ID_Last and Study_id actually text fields? If not, they shouldn't have single quotes around them if this is being done on linked tables from an Access database.

 

[thr33xx]

Are Coor_ID_Last and Study_id actually text fields? If not, they shouldn't have single quotes around them if this is being done on linked tables from an Access database.

Hi Bob,

Coor_ID_Last and Study_ID are text fields. Yes, tables are on the SQL server with Access serving as the front end.

 

[thr33xx]

Figured it out!

Silly comma somehow slipped itself in there! I know I didn't type it!

Private Sub Add_New_Study_Click()
Dim sqlstr As String
Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection


'Updates/Save changes to study info
sqlstr = "Update dbo_Setup "
sqlstr = sqlstr & "Set Coor_ID_Last ='" & Me.Coor_ID_Last & "'[B][COLOR="Red"][SIZE="5"],[/SIZE][/COLOR][/B] where Study_id = '" & Me.Add_Study_ID & "' "
MsgBox sqlstr
cn.Execute sqlstr

cn.Close
Set cn = Nothing

'Call Add_Study_ID_AfterUpdate    'refresh
MsgBox "Save successful."

End Sub