Go to Top

Maximize the use of TempVars in Access 2007 and 2010

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.

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

13 Responses to "Maximize the use of TempVars in Access 2007 and 2010"

  • samba
    June 18, 2016 - 2:03 am Reply

    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

  • bart louwagie
    August 29, 2013 - 2:53 am Reply

    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?

    • Ben Clothier
      September 10, 2013 - 5:56 pm Reply

      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.

  • Al
    September 5, 2012 - 11:54 pm Reply

    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

    • Juan Soto
      September 6, 2012 - 9:20 pm Reply

      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!

  • TPratt
    August 6, 2012 - 6:09 pm Reply

    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?

    • Juan Soto
      August 6, 2012 - 7:55 pm Reply

      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/

  • Byron Downey
    March 31, 2012 - 2:00 am Reply

    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

    • Juan Soto
      March 31, 2012 - 2:04 am Reply

      Hi Byron,

      Glad to hear you love the technique! Make sure the tempvars are populated before opening the query.

      Kind Regards,
      Juan

  • Tommy Popick
    March 8, 2012 - 11:29 am Reply

    Thanks Man, you are great. I was searching for this from last week.

  • Tom
    January 27, 2012 - 8:48 am Reply

    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.

  • Store multi-session variables in Access permanently | Juan Soto's Blog
    January 12, 2011 - 11:55 am Reply

    […] 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 […]

  • Using Access queries vs SQL Server queries | Juan Soto's Blog
    December 29, 2010 - 2:11 am Reply

    […] you’re using custom functions, TempVars or other Access specific techniques. Let’s face it, if you’re reading this blog you may […]

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.