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?
Microsoft ApplicationsMicrosoft AccessMicrosoft Office

Avatar of undefined
Last Comment
geeta_m9

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Unlike Global variables, you can refer to the TempVar() collection from a Query - like you would a Function call to retrieve your global.

mx
geeta_m9

ASKER
Yes, I am using Access 2010.
geeta_m9

ASKER
I did what you suggested. So in the Query Design would the variable look like like this:

[TempVars]![MonthVar]
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
IrogSinta

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
IrogSinta

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
geeta_m9

ASKER
One more thing, how do I initialize my TempVar item?
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Example:

Private Sub Form_Load()
    TempVars.Add ("YourVarName", <SomeValue>)
End Sub

Basically, wherever you would have initialized your Global variable ...

mx
Your help has saved me hundreds of hours of internet surfing.
fblack61
geeta_m9

ASKER
Thanks