Link to home
Start Free TrialLog in
Avatar of geeta_m9
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
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Unlike Global variables, you can refer to the TempVar() collection from a Query - like you would a Function call to retrieve your global.

mx
Avatar of geeta_m9
geeta_m9

ASKER

Yes, I am using Access 2010.
I did what you suggested. So in the Query Design would the variable look like like this:

[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)
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
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
Thanks