Sql update query string converted to db.execute not working

[sandman]

The following query string works perfectly when run from a command button.




Dim sqlSTR As String
sqlSTR = "UPDATE Table1 SET Table1.[attachment] = [Forms]![Form1]![attach] WHERE Table1.[ref]= [Forms]![Form1]![ref];"
DoCmd.RunSQL sqlSTR


I have tried with many variations of cocatenate on the reference fields (Form1.attach) and (Form1.ref) in the string SqlSTR which I used in the reference of strSQL in db.execute strSQL,dbFailOnError
but they all continually fail.

I get runtime error 424 Object required.


Some help is much appreciated

[Welshgasman]

No, I reckon you need to concatenate.

Put it ALL into a string variable and debug.print that variable.

If you still cannot get it to work, post back with the output of the debug.print and what type of value the form control holds, number, string or date.

Is it actually an attachment as well?

Edit: If actually an attachment, that apparently is not the way to do it.

I have Google on this laptop and a quick search brought up https://stackoverflow.com/questions/...o-access-table

[sandman]

Thanks for your help,

This is the result of the debug.print. its exactly the same as the original sqlSTR

UPDATE Table1 SET Table1.[attachment] = [Forms]![Form1]![attach] WHERE Table1.[ref]= [Forms]![Form1]![ref];

No it is not an attachment, all the field references are set as text as are the table fields

[Minty]

If the form is open the simply concatenate the values.

sqlSTR = "UPDATE Table1 SET Table1.[attachment] = '" & Me.[attach] & "' WHERE Table1.[ref]= '" & Me.[ref] & "'"

Debug.Print sqlSTR

I have assumed both are strings.

The VBA compiler doesn't understand the form reference in the shape of [Forms]![Form1]![attach].
Saved queries do and DoCmd.RunSQL is invoking the same interpreted engine.

[Micron]

I might not be expressing this with the proper terminology but I believe the first works because RunSql uses the query 'engine' where such form references work. You can prove that by using form references in a test query. The latter method does not work the same way. .Execute is a method of Application.CurrentDb which uses ACE (formerly JET?) whereas RunSql is a method of the DoCmd object. If anyone has the exact skinny on this, please post it.

[sandman]

Minty your answer is what appears to be almost there, it throws a wobbler when I use your exact string so

I modified it to
sqlSTR = "UPDATE Table1 SET Table1.[attachment] = " & Me.[attach] & " WHERE Table1.[ref]= " & Me.[ref] & ";"

When i run it the debug.print shows as

UPDATE Table1 SET Table1.[attachment] = testing WHERE Table1.[ref]= a;

This is exactly what is in the text boxes on the form.

However the table doesn't update and the Vb error message is "3061 to few parameters. Expected 2"

Any ideas??

and debug refers to CurrentDb.Execute sqlSTR, dbFailOnError

[Minty]

You need to include the string separators I put in.

sqlSTR = "UPDATE Table1 SET Table1.[attachment] = '" & Me.[attach] & "' WHERE Table1.[ref]= '" & Me.[ref] & "'"

Which should generate

UPDATE Table1 SET Table1.[attachment] = 'testing' WHERE Table1.[ref]= 'a'

You don't need the semi colon on the end.

[Welshgasman]

Can you see them now?

sqlSTR = "UPDATE Table1 SET Table1.[attachment] = '" & Me.[attach] & "' WHERE Table1.[ref]= '" & Me.[ref] & "'"

[sandman]

Yep it works like a charm. Thanks to you both for the assist. it was the delimiters that were missing or out of position. "Lesson learned". Sorry I didn't answer sooner, it was 3am here when I last posted.