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

Variable retaining value from InputBox

P: 22
I am importing Excel files into Access and trying to automate queries in order to manipulate and create reports. In a subroutine, several queries are run to CREATE and UPDATE a TEMP table.

In the beginning of a subroutine the user is asked for a variable, MyValue. The code is:
Expand|Select|Wrap|Line Numbers
  1.    'Dim MyValue As Variant
  2.    MyValue = InputBox("1. Enter Sheet #", "MyInputbox")
  3.    tblname = "Sheet" & MyValue
  4.  
Later in the same subroutine the variable MyValue is used in an UPDATE query. The code is:
Expand|Select|Wrap|Line Numbers
  1.    strSql = "UPDATE [" & tblname & "] SET MyValue1 = MyValue WHERE MyValue1 Is Null"
  2.    DoCmd.RunSQL strSql
  3.  
Why is Access putting a box up before the UPDATE query? The box indicates 'enter parameter' and asks 'Enter MyValue'?
Thanks,
Carl
Apr 18 '12 #1

✓ answered by NeoPa

Try using :
Expand|Select|Wrap|Line Numbers
  1. strSql = "UPDATE [" & tblname & "] SET [MyValue1] = " & MyValue & " WHERE MyValue1 Is Null"
This is assuming your SQL syntax was correct and that MyValue is a numeric value. If it's a string then the SQL syntax is different and your VBA should then be :
Expand|Select|Wrap|Line Numbers
  1. strSql = "UPDATE [" & tblname & "] SET [MyValue1] = '" & MyValue & "' WHERE MyValue1 Is Null"

Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,489
Because MyValue, as defined in the first set of code is a VBA variable that has scope within that module only (according to what you include, which is unfortunately inadequate for the question.), but you are attempting to access it from within the Jet SQL engine. This has no concept of any variables defined within VBA so asks for you to provide a value for it to use.
Apr 18 '12 #2

P: 22
Another interesting item occurs when I add the following code just prior to the UPDATE query:
Expand|Select|Wrap|Line Numbers
  1. MyValue = InputBox("1. Enter Sheet #", "MyInputbox")
.
I find it interesting that the same box comes up and indicates 'enter parameter' and asks 'Enter MyValue'? Is there a method of avoiding answering the MyValue question.
Thanks,
Carl
Apr 18 '12 #3

NeoPa
Expert Mod 15k+
P: 31,489
Try using :
Expand|Select|Wrap|Line Numbers
  1. strSql = "UPDATE [" & tblname & "] SET [MyValue1] = " & MyValue & " WHERE MyValue1 Is Null"
This is assuming your SQL syntax was correct and that MyValue is a numeric value. If it's a string then the SQL syntax is different and your VBA should then be :
Expand|Select|Wrap|Line Numbers
  1. strSql = "UPDATE [" & tblname & "] SET [MyValue1] = '" & MyValue & "' WHERE MyValue1 Is Null"
Apr 18 '12 #4

P: 22
Thanks for your help. The code worked!!!!! It seems that your answer presents the variable as a variable with expanded wildcard type information. If there is an article on this, please direct me to it. Your answer should be marked as best response but I do not see the button on my screen.
Thanks again,
Carl23
Apr 22 '12 #5

NeoPa
Expert Mod 15k+
P: 31,489
It doesn't pertain to wildcards, but to VBA code Carl.

What you need to do to understand this is appreciate that there are two sections to it :
  1. Formulate a SQL string in VBA. Although this may contain SQL commands, at this point it is simply a VBA string. Because you're using VBA you can add items to this string from anything which is available to the VBA. VBA variables, for instance, are obviously accessible to VBA, but would not be accessible to the Jet SQL engine. We can use VBA though, to formulate a string with literals in it to represent values. Have a look at Quotes (') and Double-Quotes (") - Where and When to use them.
  2. Send the resultant string (which at this stage is simply a string value available to VBA - typically stored in a VBA variable) to the Jet SQL engine for the contents to be interpreted by that.

PS. I guess you found the Best Answer button. Thank you :-)
Apr 22 '12 #6

Post your reply

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