Edit Command Button w/ SQL

[mchris]

I have an edit button that when clicked should edit the values in my tblContacts. The code attached to the button is:

Private Sub cmdEdit_Click()
On Error GoTo Err_cmdEdit_Click

Dim strSQL As String

strSQL = "UPDATE tblContacts SET"
strSQL = strSQL & "chrFirstName = " & Me.txtFirstName.Value + "',"
strSQL = strSQL & "chrLastName = " & "'" & Me.txtLastName.Value + "',"
strSQL = strSQL & "chrAddress1 = " & "'" & Me.txtAddress1.Value + "',"
strSQL = strSQL & "chrAddress2 = " & "'" & Me.txtAddress2.Value + "',"
strSQL = strSQL & "chrCity = " & "'" & Me.txtCity.Value + "',"
strSQL = strSQL & "chrState = " & "'" & Me.txtState.Value + "',"
strSQL = strSQL & "chrZipCode = " & "'" & Me.txtZipCode.Value + "',"
strSQL = strSQL & "chrPhoneNumber = " & "'" & Me.txtPhoneNumber.Value + "',"
strSQL = strSQL & "WHERE lngContactID = Me.txtCID.Value"

DoCmd.RunSQL strSQL

Exit_cmdEdit_Click:
    Exit Sub

Err_cmdEdit_Click:
    MsgBox Err.Description
    Resume Exit_cmdEdit_Click
    
End Sub

When clicked, I get an error message saying "Syntax Error in UPDATE statement"

Any help????

Matt

 

[namliam]

strSQL = "UPDATE tblContacts SET"
strSQL = strSQL & "chrFirstName = " & Me.txtFirstName.Value + "',"
strSQL = strSQL & "chrLastName = " & "'" & Me.txtLastName.Value + "',"
strSQL = strSQL & "chrAddress1 = " & "'" & Me.txtAddress1.Value + "',"
strSQL = strSQL & "chrAddress2 = " & "'" & Me.txtAddress2.Value + "',"
strSQL = strSQL & "chrCity = " & "'" & Me.txtCity.Value + "',"
strSQL = strSQL & "chrState = " & "'" & Me.txtState.Value + "',"
strSQL = strSQL & "chrZipCode = " & "'" & Me.txtZipCode.Value + "',"
strSQL = strSQL & "chrPhoneNumber = " & "'" & Me.txtPhoneNumber.Value + "',"
strSQL = strSQL & "WHERE lngContactID = Me.txtCID.Value"

What wrong:
Missing a space at the end of the first line (between SET and ")
Missing an opening ' on the first name
Use all & no +
You need to use the & format as where in the where clause.

The corrected code should look something like:
strSQL = "UPDATE tblContacts SET "
strSQL = strSQL & " chrFirstName = " & "'" & Me.txtFirstName.Value & "', "
strSQL = strSQL & " chrLastName = " & "'" & Me.txtLastName.Value & "', "
strSQL = strSQL & " chrAddress1 = " & "'" & Me.txtAddress1.Value & "', "
strSQL = strSQL & " chrAddress2 = " & "'" & Me.txtAddress2.Value & "', "
strSQL = strSQL & " chrCity = " & "'" & Me.txtCity.Value & "', "
strSQL = strSQL & " chrState = " & "'" & Me.txtState.Value & "', "
strSQL = strSQL & " chrZipCode = " & "'" & Me.txtZipCode.Value "', "
strSQL = strSQL & " chrPhoneNumber = " & "'" & Me.txtPhoneNumber.Value & "', "
strSQL = strSQL & " WHERE lngContactID = " & Me.txtCID.Value

Tip1: Try adding a debug.print before the docmd.runsql so you can see the full generated SQL
Tip2: Dont use Docmd.Runsql but use Currentdb.Execute
Tip3: Allways start and end a line in sql with a space, that way you dont run the risk of getting something like you had. The SQL you created looked like so:
SETchrFirstName
Which will obviously create an error, where a double space wouldnt hurt anyone: SET chrFirstName

Greetz

 

[mchris]

Hmmmm.

Mailman-

thanks for the quick reply and tips...much appreciated.

I updated the code as you suggested...it is now:

Private Sub cmdEdit_Click()
On Error GoTo Err_cmdEdit_Click

Dim strSQL As String

strSQL = "UPDATE tblContacts SET "
strSQL = strSQL & " chrFirstName = " & "'" & Me.txtFirstName.Value & "', "
strSQL = strSQL & " chrLastName = " & "'" & Me.txtLastName.Value & "', "
strSQL = strSQL & " chrAddress1 = " & "'" & Me.txtAddress1.Value & "', "
strSQL = strSQL & " chrAddress2 = " & "'" & Me.txtAddress2.Value & "', "
strSQL = strSQL & " chrCity = " & "'" & Me.txtCity.Value & "', "
strSQL = strSQL & " chrState = " & "'" & Me.txtState.Value & "', "
strSQL = strSQL & " chrZipCode = " & "'" & Me.txtZipCode.Value & "',"
strSQL = strSQL & " chrPhoneNumber = " & "'" & Me.txtPhoneNumber.Value & "', "
strSQL = strSQL & " WHERE lngContactID = " & Me.txtCID.Value

debug.Print

CurrentDb.Execute strSQL

Exit_cmdEdit_Click:
    Exit Sub

Err_cmdEdit_Click:
    MsgBox Err.Description
    Resume Exit_cmdEdit_Click
    
End Sub

When I click edit, I get a window saying the same as before...

Any suggestions???

 

[namliam]

try changing the debug.print to msgbox, you will get a popup with the SQL

Might it be that one of the values is Null? Try placing Nz(me.....,"") for every field.

Greetz

 

[mchris]

It could be. The chrAddress2 field can sometimes be null, and is in my test example? Need I change anything to accomadate this?

Also, I don't understand what you mean by Nz(me....),""

Is this what you mean...for each line:

strSQL = strSQL & " chrFirstName = " & "'" & Nz(Me.txtFirstName.Value & "',)"

Thanks a million.

Mat

 

[mchris]

Resolved

GOT IT.....

the last line.....

'WAS THIS

strSQL = strSQL & " chrPhoneNumber = " & "'" & Me.txtPhoneNumber.Value & "', "

'SHOULD BE THIS

strSQL = strSQL & "chrPhoneNumber = " & "'" & Me.txtPhoneNumber.Value & "'"

How bout that???

Thanks!

 

[namliam]

Good, now remember... START and END with a space!

 

[mchris]

Thanks for the advice...I will heed it.

I just started learning access and VBA like two weeks ago...it's a slow process, but I'm catching on.

 

[microkaren]

I've also tried that code cause I also want to have an edit command to my Database,but everytime I click it, I get a window saying method or data member not found

 

[spikepl]

what exact code? If you do not show what you have done EXACTLY then we cannot help you.

 

[microkaren]

I've already got the correct code .Thank you for the help