Solved

How to resolve the error "Run-time error '-2147217900(80040e14): Synax error in UPDATE statement using Access 2003?

Posted on 2013-07-01
6
4,172 Views
Last Modified: 2013-07-01
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
Comment
Question by:zimmer9
6 Comments
 

Author Comment

by:zimmer9
ID: 39291916
Guess the answer is the autonumber should not be in quotes
0
 

Author Comment

by:zimmer9
ID: 39291925
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
 

Author Comment

by:zimmer9
ID: 39291933
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 9

Expert Comment

by:edtechdba
ID: 39291937
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39291967
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39292028
try placing the word "password" in brackets


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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now