DoCmd.RunSQL "UPDATE error 2

[Zeroanarchy]

Ok having a bit of a prob with the below code any one got any ideas? It keeps pointing to "Linked,
error message: expected end of statement

I thought maybe "linked" was a reserved word but it does not seem to be.

This is the code

DoCmd.RunSQL "UPDATE s_Visithistory SET s_Visithistory.issuenote = "Linked to issue no. " & [Forms]![Form1]![Submain].[Form]![sitesubform].[Form]![issuecode]WHERE (((s_Visithistory.keyrow)=[Forms]![Form1]![Submain].[Form]![sitesubform].[Form]![Mainno]))"

I tried the below code but still no go.

DoCmd.RunSQL "UPDATE s_Visithistory SET s_Visithistory.issuenote = ('"Linked to issue no. "' & '"[Forms]![Form1]![Submain].[Form]![sitesubform].[Form]![issuecode]"')WHERE (((s_Visithistory.keyrow)=[Forms]![Form1]![Submain].[Form]![sitesubform].[Form]![Mainno]))"

any ideas?

Thanks for your time

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[jdwuk2003]

The problem is with the " character.
Try this:

Dim myString1 as string
Dim myString2 as string

myString1 = "Linked to issue no. " & [Forms]![Form1]![Submain].[Form]![sitesubform].[Form]![issuecode]
myString2 = [Forms]![Form1]![Submain].[Form]![sitesubform].[Form]![Mainno]
DoCmd.RunSQL "UPDATE s_Visithistory SET s_Visithistory.issuenote = """ & myString1 & """ WHERE s_Visithistory.keyrow = """ & myString2 & """;"

 

[Norris68]

Either that or use single quotes.

DoCmd.RunSQL "UPDATE s_Visithistory SET s_Visithistory.issuenote = 'Linked to issue no. " & [Forms]![Form1]![Submain].[Form]![sitesubform].[Form]![issuecode] & "' WHERE (((s_Visithistory.keyrow)=" & [Forms]![Form1]![Submain].[Form]![sitesubform].[Form]![Mainno]& &quot)"

I have also substituted the form value in the WHERE clause with the actual text - will help the query processor. I try to avoid making the query look up form values if at all possible.

 

[Zeroanarchy]

Thankyou both very much for your time, I was getting a bit of a head ache from working on this. The idea of not looking up form values is a good one I think I will have to pay more attention to that in the future.

Cheers


ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.