zimmer9
asked on
How to resolve the error "Run-time error '-2147217900(80040e14): Synax error in UPDATE statement using Access 2003?
I am developing an Access application in Access 2003 using an MDB type file.
In the following VBA code, I am getting the error:
Run time error '-2147217900(80040e14);
Syntax error UPDATE statement.
Do you know how I can resolve this error?
And I performed the following display while debugging my code:
?ssql
update tblUserSecurity1 set password= 'Martin29' WHERE recordnum='1'
recordnum is an AutoNumber Data Type field.
Private Sub Command34_Click()
Dim strSQL As String
Dim strSQLFull As String
Dim ssql As String
Dim rst As ADODB.Recordset
Dim con As ADODB.Connection
If Me.TxtPwd.Value = DLookup("password", "tblUserSecurity1", "[recordnum]=" & Me.Combo25.Value) Then
Else
MsgBox "Incorrect password!"
Exit Sub
End If
If Nz(Me!Text28, "") <> Nz(Me!Text30, "") Then
MsgBox "Passwords don't match!"
Exit Sub
End If
If Nz(Me!Text28, "") = "" Then
MsgBox "Please type in a new password!"
Exit Sub
End If
Set con = CurrentProject.Connection
Set rst = New ADODB.Recordset
ssql = "update tblUserSecurity1 set password= '" & Me!Text28 & "' WHERE recordnum='" & Me!Combo25.Value & "'"
rst.Open ssql, con
End Sub
In the following VBA code, I am getting the error:
Run time error '-2147217900(80040e14);
Syntax error UPDATE statement.
Do you know how I can resolve this error?
And I performed the following display while debugging my code:
?ssql
update tblUserSecurity1 set password= 'Martin29' WHERE recordnum='1'
recordnum is an AutoNumber Data Type field.
Private Sub Command34_Click()
Dim strSQL As String
Dim strSQLFull As String
Dim ssql As String
Dim rst As ADODB.Recordset
Dim con As ADODB.Connection
If Me.TxtPwd.Value = DLookup("password", "tblUserSecurity1", "[recordnum]=" & Me.Combo25.Value) Then
Else
MsgBox "Incorrect password!"
Exit Sub
End If
If Nz(Me!Text28, "") <> Nz(Me!Text30, "") Then
MsgBox "Passwords don't match!"
Exit Sub
End If
If Nz(Me!Text28, "") = "" Then
MsgBox "Please type in a new password!"
Exit Sub
End If
Set con = CurrentProject.Connection
Set rst = New ADODB.Recordset
ssql = "update tblUserSecurity1 set password= '" & Me!Text28 & "' WHERE recordnum='" & Me!Combo25.Value & "'"
rst.Open ssql, con
End Sub
ASKER
ssql = "update tblUserSecurity1 set password= '" & Me!Text28 & "' WHERE recordnum= " & Me!Combo25.Value & ""
rst.Open ssql, con
generates:
?ssql
update tblUserSecurity1 set password= 'Martin29' WHERE recordnum= 1
yet I still get the same error:
Run time error '-2147217900(80040e14);
Syntax error UPDATE statement.
rst.Open ssql, con
generates:
?ssql
update tblUserSecurity1 set password= 'Martin29' WHERE recordnum= 1
yet I still get the same error:
Run time error '-2147217900(80040e14);
Syntax error UPDATE statement.
ASKER
However, if I place the following:
update tblUserSecurity1 set password= 'Martin29' WHERE recordnum= 1
manually in a query object, the code works. The system states:
You are about to update 1 row(s)
Once you click YES, you can't use the UNDO command to reverse the changes.
Are you sure you want to update these records.
update tblUserSecurity1 set password= 'Martin29' WHERE recordnum= 1
manually in a query object, the code works. The system states:
You are about to update 1 row(s)
Once you click YES, you can't use the UNDO command to reverse the changes.
Are you sure you want to update these records.
Are you all set? If not, you may want to check out this update statement, it should work.
ssql = "UPDATE tblUserSecurity1 SET password = '" & Me!Text28.Value & "' " & _
"WHERE recordnum = '" & Me!Combo25.Value & "'"
ssql = "UPDATE tblUserSecurity1 SET password = '" & Me!Text28.Value & "' " & _
"WHERE recordnum = '" & Me!Combo25.Value & "'"
You're right about the recordnum not being in quotes; so you don't need the ending quotes either:
So your whole code can be shortened to this:
ssql = "update tblUserSecurity1 set password= '" & Me!Text28 & "' WHERE recordnum= " & Me!Combo25.Value
This section If Me.TxtPwd.Value = DLookup("password", "tblUserSecurity1", "[recordnum]=" & Me.Combo25.Value) Then
Else
MsgBox "Incorrect password!"
Exit Sub
End If
can be shortened this way:
If Me.TxtPwd.Value <> DLookup("password", "tblUserSecurity1", "[recordnum]=" & Me.Combo25.Value) Then
MsgBox "Incorrect password!"
Exit Sub
End If
I don't see the need for a recordset since you are not doing anything with it. To run an Update SQL statement you need to use the Execute method.So your whole code can be shortened to this:
If Me.TxtPwd.Value <> DLookup("password", "tblUserSecurity1", "[recordnum]=" & Me.Combo25.Value) Then
MsgBox "Incorrect password!"
Else If Nz(Me!Text28, "") <> Nz(Me!Text30, "") Then
MsgBox "Passwords don't match!"
Else If Nz(Me!Text28, "") = "" Then
MsgBox "Please type in a new password!"
Else
Set con = CurrentProject.Connection
ssql = "update tblUserSecurity1 set password= '" & Me!Text28 & "' WHERE recordnum= " & Me!Combo25.Value
con.Execute ssql
End If
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER