Solved

Using an external variable as a field value

Posted on 2013-01-07
12
504 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
  • 6
  • 4
  • 2
12 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 300 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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 100 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

813 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now