The TempVars collection has the ability to save developers time, improve the reliability of your programming and provide you with new ways to share information across your application.
TempVars were introduced starting in Access 2007 and represented a major addition to the arsenal of tools for professional developers. With it developers can now store their global variables in a stable environment that will retain values regardless of program crashes.
Before TempVars developers had limited choices when using application wide variables: module level variables; hidden forms or tables. Their are drawbacks to each approach, with module variables being the worst since they initialize if your program encounters an error; losing their assigned values in the process.
Use TempVars Everywhere
TempVars have no such limitation, they retain their values when your code encounters an error and can be used in Forms, Queries, Reports and code. You initialize them when your application starts and they disappear when your application terminates. In short, their a great way to share information across your program!
TempVars Collection
Add Method: Use it to add variables to the collection, all variables are of the variant type.
TempVars.Add Name, Value
Where Name is the name of your variable and Value is of course the value you wish to assign it. Some examples:
TempVars.Add “strAppName”, “Test Application”
TempVars.Add “lngClientID”, 123456789
TempVars.Add “bolLoginAgain”, True
Once assigned you can now use the value in your queries and code using the following methods:
TempVars!strAppName
TempVars.Item(“strAppName”)
TempVars.Item(0)
All three methods above would return “Test Application”.
Remove Method
Use this method to remove the variable or use RemoveAll to get rid of all of them.
Queries
One particular advantage of using TempVars is their availability in queries, which you can’t do with global variables without using a function. I created an entire multi-franchise database system using TempVars to differentiate between franchisee data, it made my life easier and improved the reliability of my application.
Conclusion
I encourage you to start using TempVars in your code and your queries in place of module variables or classes, providing your users with a better experience and making your code more powerful.
I’m new to Access and learning VBA as well. I have a form (in DATASHEET MODE) that the users will paste multiple records into (from an excel sheet). I need to validate a specific field in the data that they paste before writing it out to a table. I need to make sure the IDs they enter, are valid (by comparing it to a ID field in a tblMembers on sql server). If the ID is available in that said tblMembers, they allow the writing out to occur, else return the IDs that are not valid.
Is this possible using tempvars (for each of the rows they enter)….I’ve researched a lot, and most ppl suggest recordsets. I do not have any knowledge of recordsets and I’m on a time crunch now.
Please help
Hi
Condition column: where (([Table]![FieldB]) like “[A]*”)
could need to be written as where (([Table]![FieldB]) like “[A]%”)
The wildcard character differs depending on some ANSI SQL related setting. At least in our environment, a default ACCESS 2010 installation we need to use ‘%” for wildcard.
I greatfull for the Ctrl-G tip above but cannot find a way to add TempVars there. How does one do that in that interface?
Bart –
Note that when using ANSI SQL-92 metacharacters (e.g. “%”), Access will use “ALIKE”, not “LIKE” function. When you set a database to use SQL-92, any new queries will default to “ALIKE” and change “LIKE” to “ALIKE”. But you can use the ALIKE even in a database without setting it to SQL-92 mode.
As for the adding Tempvars, you would actually do it in the code. For example, if you need to hold a username from the startup, you would do so, assuming the code is placed on the form’s module:
TempVars.Add “UserName”, Me.txtUserName
I hope that helps.
Hello Juan,
I’m trying to create a little database and then reporting for it.
I’m a novice to Access and have a question if that’s ok.
The scenario is as follows:
I get data from a CUBE
I create/add it to my access dbase
I add a date field using a query (it works)
I then need to select a field from one of the imported lines and copy it into another field for all the imported lines.
To do so using a TempVar sounds like a good idea, please correct me if i’m wrong…
I’ve tried all sorts of arguments using all sorts of syntax, by directly typing them in to the condition column or using the generator with no success, can you please give me the right syntax.
Action column: DefineVarTemp
Arguments column: tot_value; =[Table]![FieldA]
Condition column: where (([Table]![FieldB]) like “[A]*”)
my condition being: fieldb’s first character must be an A. I’m not sure if i can use where.
To make things simplier, my version of access is in french so some of the language varies.
On your post you mention adding:
TempVars.Add “strAppName”, “Test Application” Name & Value
It sounds really simple, is there anywhere i can simply type that in? in a query in sql mode? a macro?…
Does a site or guide explaining access with simplistic examples for neophytes like me exist?
Your help will be highly appreciated.
Many thanks
Alastair
Looks like your syntax is off, try using the following where clause:
Where FieldB Like “A*”
In regards to setting a TempVar, you can do so by pressing Ctrl-G and using:
TempVars.Add “TempVarName”, “TempVarValue”
Hope that helps!
Juan – Thanks for posting this info. Question though. In a multiuser environment, are TempVars specific to the user, or to the application? Example: I have a front-end access 2007 db linked to SQL tables. I do not require a “login” to the FE, but rather load their UserName and MachineName into Tempvars, which will serve to determine what they can and cannot see on certain forms. Will those tempvars remain for each instance of the FE, or will it be rewritten each time someone else opens the database?
Yes, if you set the tempvars using just that user’s info then they are unique to each session. That’s how we do multitenancy applications here at IT Impact. If you however need multi-session variables consider using ReadGV and WriteGV functions, which you can ready here: http://accessexperts.net/blog/2011/01/12/multi-session-global-variables/
Hi Jaun, love your tempvars technique ! – hit a little glitch when executing a query (with tempvar criteria) thru the application object.
CurrentDb.Execute psQryNme – the query returns “3061-Too few parameters. Expected 2”.( I’m setting two tempvars specified in criteria ) — Executes when run manually.. Also tried setting tempvars as parameters.. Any idears ? Thx, Byron
Hi Byron,
Glad to hear you love the technique! Make sure the tempvars are populated before opening the query.
Kind Regards,
Juan
Thanks Man, you are great. I was searching for this from last week.
I haven’t used TempVars yet but after your post I think they have use in queries. Ugly name though: it suggest volatile storage, while they are global. The “stable environment” argument does not go very far for me: globals only reset while debugging, not in Production.
[…] need multi-session If your looking for a reliable global variable than look no further: use TempVars in your code. But they don’t last between sessions so I came up with a hearty replacement I […]
[…] you’re using custom functions, TempVars or other Access specific techniques. Let’s face it, if you’re reading this blog you may […]