geeta_m9
asked on
Using an external variable as a field value
I wish to update the values in a column in a field in a table using an update query with the value of a variable called MonthVar which was declared in a module called modFunctions. How would I refer to this variable?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, I am using Access 2010.
ASKER
I did what you suggested. So in the Query Design would the variable look like like this:
[TempVars]![MonthVar]
[TempVars]![MonthVar]
Then TempVars() is the slickest way to go.
Basically ... set your tempvar item (where ever)
TempVars.Add "SomeVarName", <value>
In your query - create an expression (or criteria) that retrieves the TempVar var using
TempVars(SomeVarName)
Basically ... set your tempvar item (where ever)
TempVars.Add "SomeVarName", <value>
In your query - create an expression (or criteria) that retrieves the TempVar var using
TempVars(SomeVarName)
Two examples:
SELECT Table1.FIELD1, Table1.FIELD2
FROM Table1
WHERE (((Table1.FIELD3)=TempVars("YourVarName")));
SELECT Table1.FIELD1, Table1.FIELD2, TempVars("YourVarName") AS GetMyVar
FROM Table1;
Remember ... somewhere you have to initialize the TempVar item ... maybe in the On Load event of a Form, etc.
mx
SELECT Table1.FIELD1, Table1.FIELD2
FROM Table1
WHERE (((Table1.FIELD3)=TempVars("YourVarName")));
SELECT Table1.FIELD1, Table1.FIELD2, TempVars("YourVarName") AS GetMyVar
FROM Table1;
Remember ... somewhere you have to initialize the TempVar item ... maybe in the On Load event of a Form, etc.
mx
Another super cool thing about TempVars. IF ... an error occurs and a reset occurs - this does NOT hose the TempVar collection - just like if they were in a table.
mx
mx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I guess I should have refreshed this page first. :-)
The other cool thing about TempVars is that you don't have to declare it in a module for it to be available globally.
Ron
The other cool thing about TempVars is that you don't have to declare it in a module for it to be available globally.
Ron
ASKER
One more thing, how do I initialize my TempVar item?
Example:
Private Sub Form_Load()
TempVars.Add ("YourVarName", <SomeValue>)
End Sub
Basically, wherever you would have initialized your Global variable ...
mx
Private Sub Form_Load()
TempVars.Add ("YourVarName", <SomeValue>)
End Sub
Basically, wherever you would have initialized your Global variable ...
mx
ASKER
Thanks
mx