Update table but field contains ' character

[bigrods]

Hi all,

Hope this thread is posted in the right place....

Just wanted a bit of help with some Access VBA; I've got a table I'm trying to update via an unbound form.

My code falls over when trying to update a record for a person with a ' in their name i.e. O'Neil, O'Mara etc.

Does anyone know the syntax for updating a table in Access VBA where a field contains a '?

strSQL = "UPDATE tbl_Death SET Death_Name = '" & Me.txtName & "' WHERE tbl_Death.Death_ID = " & Me.txtDeathID & ""

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

Many thanks!

 

[Minty]

I would set a string variable to be your me.txtName then enclose that in triple quotes
Air code !

strName = "'''" & me.txtName & "'''"

 

[bigrods]

I would set a string variable to be your me.txtName then enclose that in triple quotes
Air code !

strName = "'''" & me.txtName & "'''"

Hi,

Afraid that doesn't work, I still get "Runtime error 3075: syntax error in query expression"
Also, any people without ' in their names, I now get the same error message.

 

[pr2-eugin]

You should be using,

strSQL = "UPDATE tbl_Death SET Death_Name = " & Chr(34) & Me.txtName & Chr(34) & " WHERE tbl_Death.Death_ID = " & Me.txtDeathID

 

[bigrods]

You should be using,

strSQL = "UPDATE tbl_Death SET Death_Name = " & Chr(34) & Me.txtName & Chr(34) & " WHERE tbl_Death.Death_ID = " & Me.txtDeathID

Great, that worked a treat, thanks v much

 

[pr2-eugin]

You're welcome. The Chr(34) returns a double quotes so when the code line is executed the code will read.

UPDATE tbl_Death SET Death_Name = "Paul O'Connor" WHERE tbl_Death.Death_ID = 45

Minty's code would have worked, if he/she used double quotes inside the double quotes something like.

strSQL = "UPDATE tbl_Death SET Death_Name = """ & Me.txtName & """ WHERE tbl_Death.Death_ID = " & Me.txtDeathID

Hope that helps ! Good luck