access vba update syntax error

I have the below update query in vba where I'm trying to update the contents of a field with HTML text. I need help in how to get this string of HTML contents into the field.

This is the update statement:
DoCmd.RunSQL "UPDATE branch SET branch_text = " & [strHTML] & " WHERE branch_id = " & Form_frmReport.cboChooseBranch.value

?strhtml
<P class=MsoNormal><B style="mso-bidi-font-weight: normal"><SPAN style="COLOR: red">This is a test of the scripting system<?xml:namespace prefix = o /><o:p></o:p></SPAN></B></P>ok

I'm getting a syntax error, it looks like i may need to escape the strhtml with quotes....?
DalexanAsked:
Who is Participating?
 
Dale FyeCommented:
iTry:

strSQL = "UPDATE branch SET branch_text = """ _
            & Replace([strHTML], chr$(34), chr$(34) & chr$(34)) _
            & """ WHERE branch_id = " & Form_frmReport.cboChooseBranch.value
DoCmd.RunSQL

The string must be wrapped in quotes, which is the reason for the triple quotes before and after the Replace() function.  And in order for [strHTML] to be interpretted properly in strSQL, you must also replace the single quotes in that string with double quotes, which is the reason for the use of the Replace() function.
0
 
Michael VasilevskySolutions ArchitectCommented:
Use Replace() to replace the double quotes
0
 
DalexanAuthor Commented:
Please show the syntax within the docmd
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Michael VasilevskySolutions ArchitectCommented:
e.g.

DoCmd.RunSQL "UPDATE branch SET branch_text = " & Replace([strHTML], Chr(34), """") & " WHERE branch_id = " & Form_frmReport.cboChooseBranch.value
0
 
DalexanAuthor Commented:
I still get the same error,
0
 
Dale FyeCommented:
oops, that should be:

docmd.runsql strsql

or better yet

currentdb.execute strsql, dbfailonerror
0
 
Michael VasilevskySolutions ArchitectCommented:
fyed is correct. A single quote before the double should work as well:


DoCmd.RunSQL "UPDATE branch SET branch_text = '" & Replace([strHTML], Chr(34), """") & "' WHERE branch_id = " & Form_frmReport.cboChooseBranch.value
0
 
for_yanCommented:


perhaps using Chr(34) could help like here:

MsgBox "testing " & Chr(34) & "123" & Chr(34)

see also here about using Chr(34) annd also escaping double quote
with two double quotes:
http://www.mrexcel.com/forum/showthread.php?p=1561194






0
 
DalexanAuthor Commented:
I love experts exchange, thank you. Such a simple problem that could be solved collectively allowing me to work on other issues without spending time on this.....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.