UPDATE SQL Assistance

[cstickman]

Hello fellow users,

I am creating an update form to update my database using VBA. I am having an issue with the WHERE statement. It keeps showing up red in the code and I am not sure what I am doing wrong. Below is my code:

<code>
Private Sub cmdupdate_Click()

Dim strSQL As String

strSQL = "UPDATE settlement " & _
"SET channel = '" & cmbchannel & "', vendor = '" & cmbvendor & "', stream = '" & cmbstream & _
"', loan1 = '" & txtloan1 & "', loan2 = '" & txtloan3 & "', fname = '" & txtfname & _
"', lname = '" & txtlname & "', balance = '" & txtbalance & "', settlement = '" & txtsettlement & _
"', percentage = '" & txtpercentage & "', sstart = '" & txtsstart & "', send = '" & txtsend & _
"', sterms = '" & txtsterms & "', approved = '" & cmbapproved & "', status = '" & cmbstatus & _
"', closed = '" & txtreason & "', comments = '" & txtcomment & "', counter = '" & cmbcounter & _
"', settled = '" & cmbsettle & "', com = '" & txtcom & "', hqreview = '" & cmbhqreview & _
"', hqdate = '" & txthqdate & "', floor = '" & cmbfloor & "' & _
"WHERE ID = '" & txtid & "';

CurrentDb.Execute strSQL

If Err.Number <> 0 Then
Debug.Print strSQL
MsgBox strSQL
Exit Sub
End If

'message box
MsgBox "Your settlement has been updated successfully"

'clear the form
cmdclear_Click


End Sub
</code>

Any assistance would be greatly appreciated. Thanks

 

[AnAnalyst]

Hi there,

Shouldn't there be a double quote mark after:

"WHERE ID = '" & txtid & "';

?

 

[cstickman]

I tried your suggestion, but still getting a compile error: Expected: End of statement error.

 

[cstickman]

I figured it out - I forgot the " after cmbfloor. When you said something about the double quotes it made me start to think. So below is the code that works for anyone else who needs the help

<code>
Dim strSQL As String

strSQL = "UPDATE settlement " & _
"SET channel = '" & cmbchannel & "', vendor = '" & cmbvendor & "', stream = '" & cmbstream & _
"', loan1 = '" & txtloan1 & "', loan2 = '" & txtloan3 & "', fname = '" & txtfname & _
"', lname = '" & txtlname & "', balance = '" & txtbalance & "', settlement = '" & txtsettlement & _
"', percentage = '" & txtpercentage & "', sstart = '" & txtsstart & "', send = '" & txtsend & _
"', sterms = '" & txtsterms & "', approved = '" & cmbapproved & "', status = '" & cmbstatus & _
"', closed = '" & txtreason & "', comments = '" & txtcomment & "', counter = '" & cmbcounter & _
"', settled = '" & cmbsettle & "', com = '" & txtcom & "', hqreview = '" & cmbhqreview & _
"', hqdate = '" & txthqdate & "', floor = '" & cmbfloor & "' " & _
"WHERE ID = " & Me.txtid & ";"

DoCmd.RunSQL strSQL

If Err.Number <> 0 Then
Debug.Print strSQL
MsgBox strSQL
Exit Sub
End If

'message box
MsgBox "Your settlement has been updated successfully"

'clear the form
cmdclear_Click


End Sub
</code>

Thanks for your help!!

 

[Micron]

I suspect the issue was the lack of space between <code>cmbfloor & "' and WHERE, which you just happened to take care of with your fix. I think it would have worked if you had <code>cmbfloor & "' WHERE " & _ This is why I never use line continuation characters for sql - too easy to miss littel details as far as I'm concerned.</code></code>