Update query error 1

[jlg5454]

I have a form that you can add and edit records. Part of the edit record code keeps returning an error "syntax error on Update" here is a piece of the update code below:

strSQL = "UPDATE tbloutbound "
strSQL = strSQL & "SET Call_File_No= '" & Me!txtCallFileNum & "'"
strSQL = strSQL & ", Date= #" & Me.txtDte & "#"
strSQL = strSQL & ", Points= " & Me![txtpts]
strSQL = strSQL & ", Score= " & Me![txtScore]
strSQL = strSQL & ", Comments= '" & Me!txtComments & "'"
strSQL = strSQL & "' WHERE txtCallFileNum='" & [CboFind] & "'"
MsgBox strSQL
Debug.Print strSQL
db.Execute strSQL
Thanks for any help possible.

Joe

 

[Eupher]

Joe,

Looks like you've got an extra single quote in your SQL string:

strSQL = "UPDATE tbloutbound "
    strSQL = strSQL & "SET Call_File_No= '" & Me!txtCallFileNum & "'"
    strSQL = strSQL & ", Date= #" & Me.txtDte & "#"
    strSQL = strSQL & ", Points= " & Me![txtpts]
    strSQL = strSQL & ", Score= " & Me![txtScore]
    strSQL = strSQL & ", Comments= '" & Me!txtComments & "[highlight]'[/highlight]"
    strSQL = strSQL & "[highlight]'[/highlight] WHERE txtCallFileNum='" & [CboFind] & "'"

HTH,

Ken S.

 

[jlg5454]

Thanks,

I fixed that and still keep receiving an Update query error. Code below:

strSQL = "UPDATE tbloutbound "
strSQL = strSQL & "SET Call_File_No= '" & Me!txtCallFileNum & "'"
strSQL = strSQL & ", Date= #" & Me.txtDte & "#"
strSQL = strSQL & ", Points= " & Me![txtpts]
strSQL = strSQL & ", Score= " & Me![txtScore]
strSQL = strSQL & ", Comments= '" & Me!txtComments & "'"
strSQL = strSQL & " WHERE Call_File_No='" & [CboFind] & "'

 

[Eupher]

Joe,

Maybe this:

strSQL = strSQL & " WHERE Call_File_No=[highlight] [/highlight]'" & [b][red]Me![/red]CboFind[/b] & "'"

Ken S.

 

[jlg5454]

Thanks,

Your original reply to me works when I comment out the date portion. So I took debug string and ran a SQL query off of it. It prompts an error message stating syntax error in Update command and than has the date string highlighted. Is there something wrong with the date portion of the code?

Thanks,

Joe

 

[Eupher]

Hi, Joe,

I should have noticed that. Date is the name of a VBA function, and should be avoided as the name for a field or any other object.

HTH,

Ken S.

 

[Eupher]

Joe,

If you don't want to change the name of the date field in your table, try qualifying the name in your SQL string:

strSQL = strSQL & ", [red]tbloutbound.[/red]Date= #" & Me.txtDte & "#"

Ken S.

 

[jlg5454]

Thanks that did it! I'm learning vba on the fly and your tips were a great help.

 

[PHV]

FYI, another way:
strSQL = strSQL & ", [Date]= #" & Me.txtDte & "#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886