Variable retaining value from InputBox

[Carl23]

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:

   'Dim MyValue As Variant
   MyValue = InputBox("1. Enter Sheet #", "MyInputbox")
   tblname = "Sheet" & MyValue
 

Later in the same subroutine the variable MyValue is used in an UPDATE query. The code is:

   strSql = "UPDATE [" & tblname & "] SET MyValue1 = MyValue WHERE MyValue1 Is Null"
   DoCmd.RunSQL strSql
 

Why is Access putting a box up before the UPDATE query? The box indicates 'enter parameter' and asks 'Enter MyValue'?
Thanks,
Carl

[NeoPa]

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.

[Carl23]

Another interesting item occurs when I add the following code just prior to the UPDATE query:

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

[NeoPa]

Try using :

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 :

strSql = "UPDATE [" & tblname & "] SET [MyValue1] = '" & MyValue & "' WHERE MyValue1 Is Null"

[Carl23]

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

[NeoPa]

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 :-)