VBA RunSQL to update field in table from Form

[mickelb]

Access 2010 64Bit

I am new to Access VBA, slowly building up knowledge, and having trouble getting an After Update Event using RunSQL to work.

I have a form bound to Table1. One of thed fileds, SURNAME, has a check for any change. If a change is detected the user is asked to confirm if it is correct using a Before Update event on the SURNAME field.

If Yes then the change is saved to Table1. I also need to update the SURNAME field in Table2 for the current record ID with the new SURNAME value in the form. I am trying an After Update event using:

DoCmd.RunSQL "UPDATE TABLE2 SET [TABLE2].SURNAME =" &Me.SURNAME&" WHERE [TABLE2].ID= "&Me.ID

but keep getting a Compile Error "Expected: end of statement" and " WHERE [TABLE2].ID= " highlighted in debug.

Any help appreciated.

 

[namliam]

DoCmd.RunSQL "UPDATE TABLE2 SET [TABLE2].SURNAME =""" &Me.SURNAME&""" WHERE [TABLE2].ID= "&Me.ID

Try that

Also you may want to use currentdb.execute instead of docmd.runsql, and probably not use it for each controle but instead do the complete form in one go.

 

[JohnHBooth]

I think this would work for you:

CurrentDb.Execute "UPDATE TABLE2 SET [TABLE2].SURNAME ='" & Me.Surname & "' WHERE [TABLE2].ID= " & Me.ID, dbFailOnError

 

[mickelb]

Many thanks to both. Neither worked on their own, but a combination and changing . to ! in the Me values finally worked :

DoCmd.RunSQL "UPDATE TABLE2 SET [TABLE2].SURNAME ='" & Me!SURNAME & "' WHERE [TABLE2].ID= " & Me!ID