By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,658 Members | 1,908 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,658 IT Pros & Developers. It's quick & easy.

Setting Update Query Values as Variables

P: 6
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.
Dec 29 '07 #1
Share this Question
Share on Google+
2 Replies

P: 30
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:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "Update SomeTable Set SomeColumn = " & vbl
Dec 29 '07 #2

P: 6

Thank you very much!!! That worked!

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

Again, thank you. Problem solved.

Dec 29 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.