asked on

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
This is a test of the scripting system<?xml:namespace prefix = o /><o:p></o:p>ok

I'm getting a syntax error, it looks like i may need to escape the strhtml with quotes....?

Michael Vasilevsky

Use Replace() to replace the double quotes

Dalexan

ASKER

Please show the syntax within the docmd

Michael Vasilevsky

e.g.

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

Dalexan

ASKER

I still get the same error,

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

oops, that should be:

docmd.runsql strsql

or better yet

currentdb.execute strsql, dbfailonerror

Michael Vasilevsky

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

for_yan

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

Dalexan

ASKER

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.....