0

i have made a code that makes a text and shows that txt through a msgbox. Now i want this txt as well in a table, because i need others to copy this txt and paste it somewhere else.

In the table i made a memofield txt_GER for the german txt.

In the code i wrote:

DoCmd.RunSQL ("UPDATE Table SET txt_GER=" & txt & " WHERE ID=" & rs.Fields("Id"))

When i try to run this code i get this message: Runtime error 3075: Syntaxiserror (missing operator) in query-expression

<B>xxx</B><ul class="yyy"><li>zzz<li>

I want this txt including the html-codes into this field.

Any suggestions?

Cleo
  • 1

1 Answers1

0

Use parameters instead, e.g.:

With CurrentDb.CreateQueryDef("", "update Table set txt_GER=@txt where ID=@id")
    .Parameters(0) = txt
    .Parameters(1) = rs.Fields("Id")
    .Execute
End With

With your current code, the string delimiters present in the value held by your txt variable are terminating the SQL statement prematurely.

Lee Mac
  • 15,615
  • 6
  • 32
  • 80
  • 2
    I never worked with 'with' or with createquerydef. I now get a failure message on Parameters(0) --> 3271 --> google learned me it has probably to do with the length of the text exceeding 255 characters. So I should define the parameters in the query? https://stackoverflow.com/questions/37049626/vba-run-time-error-3271-using-dao-object – Cleo Oct 25 '20 at 13:21
  • As HansUp showed in the linked question, this doesn't work for > 255 chars. You can either use `Recordset.Edit` or your original method, using Gustav's [`CSql()` function](https://stackoverflow.com/a/36494189/3820271) when concatenating variables with SQL. It will safely encapsulate `txt`. @Cleo – Andre Oct 26 '20 at 07:31