?
Solved

Using an external variable as a field value

Posted on 2013-01-07
12
Medium Priority
?
509 Views
Last Modified: 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?
0
Comment
Question by:geeta_m9
[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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1200 total points
ID: 38752766
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
0
 
LVL 75
ID: 38752768
Unlike Global variables, you can refer to the TempVar() collection from a Query - like you would a Function call to retrieve your global.

mx
0
 

Author Comment

by:geeta_m9
ID: 38752769
Yes, I am using Access 2010.
0
10 Questions to Ask when Buying Backup Software

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.

 

Author Comment

by:geeta_m9
ID: 38752838
I did what you suggested. So in the Query Design would the variable look like like this:

[TempVars]![MonthVar]
0
 
LVL 75
ID: 38752840
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)
0
 
LVL 75
ID: 38752859
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
0
 
LVL 75
ID: 38752863
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
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 400 total points
ID: 38752882
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:
Function GetMonthVar() As Integer 'Or String or whatever data type...
   GetMonthVar = MonthVar
End Function

Open in new window

Your query then can refer to this value by using
=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

Open in new window

Then you would use it in a query this way:
=TempVars!MonthVar
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38752889
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
0
 

Author Comment

by:geeta_m9
ID: 38752941
One more thing, how do I initialize my TempVar item?
0
 
LVL 75
ID: 38752959
Example:

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

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

mx
0
 

Author Comment

by:geeta_m9
ID: 38753107
Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

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…

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.

Join & Ask a Question