Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.
COURSE of the MONTH
14 days, 8 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Using an external variable as a field value
Posted on 2013-01-07
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?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
6-
4
2
12 Comments
Active today
If you are using Access 2010 (?), I would suggest using TempVars() ... very cool.
Short tutorial by Access MVP Juan Soto
http://blogs.office.com/b/microsoft-access/archive/2010/09/27/power-tip-maximize-the-user-of-tempvars-in-access-2007-and-2010.aspx
mx
Short tutorial by Access MVP Juan Soto
http://blogs.office.com/b/microsoft-access/archive/2010/09/27/power-tip-maximize-the-user-of-tempvars-in-access-2007-and-2010.aspx
mx
Active today
Unlike Global variables, you can refer to the TempVar() collection from a Query - like you would a Function call to retrieve your global.
mx
mx
I did what you suggested. So in the Query Design would the variable look like like this:
[TempVars]![MonthVar]
[TempVars]![MonthVar]
Active today
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)
Active today
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
Active today
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
Active 6 days ago
If the variable, MonthVar, was declared at the top of the module (which would make its scope global), then you could create a function to that can be used to retrieve this value like so:
=GetMonthVar()
I agree though with MX, if you are using 2007 or newer, TempVars is the way to go. You would assign your value in your code like so:
=TempVars!MonthVar
Function GetMonthVar() As Integer 'Or String or whatever data type...
GetMonthVar = MonthVar
End Function
=GetMonthVar()
I agree though with MX, if you are using 2007 or newer, TempVars is the way to go. You would assign your value in your code like so:
TempVars("MonthVar") = 5
=TempVars!MonthVar
Active 6 days ago
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
Featured Post
Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Suggested Courses
Course of the Month14 days, 8 hours left to enroll
770 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.