Setting Update Query Values as Variables

[justapawn]

Can someone assist me in using the correct syntax or method of assigning a variable to the Set portion of a DoCmd.RunSQL Update Query?

The help file and most sites, including ms, seem to think the set new_value portion is always going to be a constant.

I've tried:

Dim vbl as String
vbl = " a calculated choice that is not on a form"
' if it were a control on a form, I can use [Forms]! ... etc.
DoCmd.RunSQL "Update SomeTable Set SomeColumn = vbl

when executing the code I'm asked to input the value of vbl.

Likewise, I've tried:

Dim vbl as String
vbl = "Update SomeTable Set SomeColumn = vbl"
DoCmd.RunSQL "vbl"

And many other variations with either syntax errors or "Enter Value for vbl"

I can't build it with the Query builder because there is no reference for the variable to be = to.

Thanks, again in advance.
JustAPawn

[zachster17]

You need to seperate the variable vbl from the string; otherwise it will be treated as a string and not a dynamic value.

Try this:

DoCmd.RunSQL "Update SomeTable Set SomeColumn = " & vbl
 

Zach

[justapawn]

Zach,

Thank you very much!!! That worked!

Something so simple and searched for hours and couldn't find it.

Again, thank you. Problem solved.

JustAPawn