Sql Update, use of quotes 1

[tag013]

Could anyone tell me what's wrong with the following code and does anyone know how to use the single and double quotes in sql and how many i.e. to for a text box, variable etc.

Dim strSql As String
strSql = "UPDATE Clients SET"
strSql = strSql & " Clients!Name = " & Me.txtName
strSql = strSql & ", Clients!Surname = " & Me.txtSurname
strSql = strSql & ", Clients.SkillId = " & Me.cboSkill
strSql = strSql & " WHERE Clients.CliID = " & Me.txtCliID
strSql = strSql & ";"
docmd.RunSql strSql

The error i get is 3075 Syntax Error (missing operator)

Thank you.

 

[SgtJarrow]

Double quotes are literal characters...meaning anything between is added to the SQL statement as typed. Single quotes are used to surround text that might appear in a field. So your code should probably be:

strSql = "UPDATE Clients SET"
strSql = strSql & " Clients!Name = '" & Me.txtName & "'"
strSql = strSql & ", Clients!Surname = '" & Me.txtSurname & "'"
strSql = strSql & ", Clients.SkillId = " & Me.cboSkill
strSql = strSql & " WHERE Clients.CliID = " & Me.txtCliID
strSql = strSql & ";"
docmd.RunSql strSql

Assuming that Clients!Name and Clients!Surname are text fields and Clients.SkillId and Clients.CliId are numeric fields.

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[tag013]

Great, Thank you, i think is working. Do you know if it is possible to turn off the message (you about to update...)?

 

[SgtJarrow]

sure....at the top of your code, place:

DoCmd.SetWarnings False

and at the bottom, put:

DoCmd.SetWarnings True

The first will turn off the warnings, the second turns it back on after updating for true system errors.

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[tag013]

Thanks you, it works perfectly!