Error

Gr3g0ry

Registered User.
Local time
Today, 07:13
Joined
Oct 12, 2017
Messages
163
i have a query that is driving me crazy.

Table Name is USERS and the structure of my table is this:

UserId - AutoNumber
FirstName - text
LastName - text
UserName - text
Password - text
Level - Text
Active - Yes/No

im trying to update with this query:

DoCmd.RunSQL "UPDATE USERS SET FirstName = '" & Me.Fname.Value & "', LastName = '" & Me.Lname.Value & "', UserName = '" & Me.uname.Value & "', Password = '" & Me.pass.Value & "', Level = '" & Me.cboType.Value & "', Active = " & Me.Active.Value & " WHERE UserID = " & Me.cboUser.Value & ";"

and getting errors. ive been trying for the last hour. please render some assistance.
 
What errors specifically?

Rather than directly running the command, assign it to a variable during concatenation and view the contents in the Immediate Window to see what is actually being sent to RunSQL.
 
here is my form and table. all im trying to do is grab all my info from the form and write it to the database. im wondering if the value in my WHERE clause affects my query being that it is an autonumber.
 

Attachments

What errors specifically?

Rather than directly running the command, assign it to a variable during concatenation and view the contents in the Immediate Window to see what is actually being sent to RunSQL.

when it crashes and gives me the option to debug, when i mouseover the statements below my UPDATE, i see where all the components have a valuee
 
There were two errors in the SQL.
1. an extra comma after the last set
2. a reserved word - Level. Reserved words should be avoided at all costs. There are various lists floating around for VBA, Access, and SQL. Or you can take your chances but be wary of simple, common words because they have likely been reserved.

Dim strSQL As String
strSQL = "UPDATE USERS SET FirstName = '" & Me.fname.Value & "', LastName = '" & Me.lname.Value & "', UserName = '" & Me.uname.Value & "', Password = '" & pass.Value & "', [Level] = '" & Me.cboType.Value & "', Active = " & Me.active.Value & " WHERE UserID = " & Me.cboUser.Value & ";"

DoCmd.RunSQL strSQL

You also have a problem waiting to happen. Last names from certain nationalities such as Irish, contain apostrophes so you CANNOT use the single quote to delimit strings that may contain apostrophes. You must use the double quote.
 
wow thanks man. so the same with double barrelled last names or hyphenated names. good looking out man. thanks
 

Users who are viewing this thread

Back
Top Bottom