Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6466
  • Last Modified:

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
0
zimmer9
Asked:
zimmer9
1 Solution
 
zimmer9Author Commented:
Guess the answer is the autonumber should not be in quotes
0
 
zimmer9Author Commented:
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.
0
 
zimmer9Author Commented:
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.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
edtechdbaCommented:
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 & "'"
0
 
IrogSintaCommented:
You're right about the recordnum not being in quotes; so you don't need the ending quotes either:
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

Open in new window

can be shortened this way:
    If Me.TxtPwd.Value <> DLookup("password", "tblUserSecurity1", "[recordnum]=" & Me.Combo25.Value) Then
       MsgBox "Incorrect password!"
       Exit Sub
    End If

Open in new window

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

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
try placing the word "password" in brackets


ssql = "update tblUserSecurity1 set [password]= '" & Me!Text28 & "' WHERE recordnum= " & Me!Combo25.Value
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now