Global Variable Disapearance in Access VBA Errors

Hi All,

Hoping someone can help me here but here goes.

In a standard module named bas_autos, I have declared the following global variables.

Option Compare Database
Option Explicit
Global APPUser          As String
Global APPUserID        As Long
Global APPUserEmail     As String
Global AppUserType      As Integer
Global AppEntID         As Integer
Global HWFingerPrint    As String

Open in new window

When my database opens, I have a userform (frm_login) that is displayed by default. All users MUST use this form to login to the database.

In doing so, I use the following code to collect those global variables at the time of successfully logging into the database.

'check temp pin and direct to change passcode ->
            If vTempPin = True Then
                APPUser = Nz(Me.cboUserName.Column(1), "")
                APPUserID = Nz(Me.cboUserName.Column(0), "")
                AppUserType = Nz(Me.cboUserName.Column(6), "")
                APPUserEmail = Nz(Me.cboUserName.Column(8), "")
                AppEntID = Nz(Me.cboEntity.Column(0), "")
                HWFingerPrint = CurrentMachineName
                DoCmd.Close 2, "frm_login"
                DoCmd.OpenForm "frm_user_pin"
                'use the settings for the user to acces and update their password ->
                Forms("frm_user_pin").Controls("txtOldPW").Value = vTempPin
                Forms("frm_user_pin").Controls("lblPin").Caption = "Change Pin"
                Forms("frm_user_pin").Controls("cmdCancel").Visible = False
                msg = "Please change your user pin by entering a new 4-digit pin that you have not used previously."
                title = "New pin required"
                style = vbOKOnly + vbInformation
                response = MsgBox(msg, style, title)
                'everything is fine just go straight to dashboard ready for session ->
                APPUser = Nz(Me.cboUserName.Column(1), "")
                APPUserID = Nz(Me.cboUserName.Column(0), "")
                AppUserType = Nz(Me.cboUserName.Column(6), "")
                APPUserEmail = Nz(Me.cboUserName.Column(8), "")
                AppEntID = Nz(Me.cboEntity.Column(0), "")
                HWFingerPrint = CurrentMachineName
                'access is okay then update current username and store global variable ->
                DoCmd.Close 2, "frm_login"
                DoCmd.OpenForm "frm_dashboard"
                'repaint the form ->
            End If

Open in new window

My problem is that if the application errors in ANY way, I keep losing these global variables that were collected at the beginning of a session.

Is there any way to circumvent this problem or am I completely missing something ?

Laila JacksonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
Error handling.  I, too, had to deal with the very same issue years ago with a relative large Access application.  I had a global variable for the current user and a run-time error would cause the global variable to lose its value.  I spent a considerable amount of time adding error handling to all procedures.  I also added a check every place the global variable was evaluated; if it was empty I called the routine that originally populated it when the app was launched.  These measures helped to resolve the issue you describe.
OM Gang
The short answer is No
You aren't missing anything, and there's no way to prevent it.
Global variables are notoriously unreliable--they always get set to null on any unhandled VBA error, and even trapped-and-disposed VBA errors can set them to null.

1. Use TempVars if you are working in versions that support them
2. Better yet, if you are working with a split frontend/backend with a distributed frontend, which you should be doing -- is create a local table to hold these values and write them to the table instead of variables.  You can then recall them via recordset as needed.
3. Hide the form instead of closing it and recall the values from the form.
4. Hide the form and refresh the global variables if they become null by checking for null just before use

The only decent way to use global variables is to check them for null before use and be able to re-initialize them if they are.  They are convenient for setting and then passing a value for immediate reuse between modules.

But as you have seen, their persistence is unreliable.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Laila JacksonAuthor Commented:
Yes its so annoying isn't it?

So essentially from what you are saying, I could either run a new login request each time the application errors (which would really annoy the crap out of my users), or, I maybe I could add a new table named tbl_current_session just to hard store the variables "in case" something goes wrong.

Seems like a lot of work but if that's all I can do then by all means I am happy to do so.

I am keen to hear a second opinion on this.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

With global variables, it is not 'in case', it's 'when' something goes wrong.
The odds that you will successfully persist a global variable for the entire life of an app's being open are close to nil.
They are a weak stick that you should not lean too heavily on lest you wind up on your face.
Laila JacksonAuthor Commented:
Wow that was fast.. thanks so much guys.

@ Nick67 - Your response provides the clarity I was searching for. It is a split fe-be application.

So, in concluding, my theory of creating another table (i.e. tbl_current_session) to hardstore the variables seems like a winner, but I also like the idea of hiding the login form as a user would never use it more than once per session.
Laila JacksonAuthor Commented:
Split points as both answers helped me achieve my solution.

Thanks heaps guys. Lai
Note that I suggest a local table.
In a distributed frontend, the local tables are peculiar to each user, and they therefore don't accidentally overwrite each other, and there is basically only a single record to contend with, keeping things small.
By far, it is the most bulletproof way of going about things.
Hiding the form can be nice -- especially if you already use a hidden form to persist connections to the backend -- but anything that causes that hidden form grief can squirrel the works up.

A table is persistent regardless of circumstances.
Dale FyeOwner, Developing Solutions LLCCommented:
Nick pointed out these two options, which are the ones I use or have used:

1.  Tempvars.  These varibles do not loose their values when you encounter an unhandled error.  The syntax for instantiating these is flexible:

Tempvars.Add "APPUser", Me.cboUserName.Column(1)


Tempvars!AppUser = Me.cboUserName.Column(1)

and you can refer to them with:

or in a query [Tempvars]![AppUser]

4.  Hide the form instead of closing it.  Then you don't even need to create the global variables and only need to refer to the control on the form, although if I was going to do this, I'd probably create hidden controls on the form and when the user selects themselves in the combo box AfterUpdate I would fill in those individual hidden textboxes.  Then you could simply refer to:


whenever you needed the Username.
I have also created hidden forms that are loaded on startup that contain all of my global variables, similar to above but whenever I need a new global variable, I add a unbound control to that form.  This allows me to unhide that form whenever I want to see the values in those fields.
Dale FyeOwner, Developing Solutions LLCCommented:
But omgang's post about error handling is the real key.  Every procedure in your application, no matter how simple, should have error handling.  This includes code behind forms, code behind reports, and standard code modules.
I've encountered global variables going south even on handled-and-disposed-of errors.
They're just not reliable.

One other REALLY advanced technique is adding custom Properties and Values to the CurrentDb.Properties collection.
I've flanged in
CurrentDb.Properties("CustomRibbonID").Value = "MyRibbon"
into apps that run A2003 through A2013.
Access 2003 could care less about the property, but uplevel versions know what to do with it.
You could do 'session variables' the same way, too.
Dale FyeOwner, Developing Solutions LLCCommented:

I use custom properties a lot.  You can create custom properties for tables, queries, forms, reports, or the database itself.  Great way to hide values which you don't want your basic users to see.  Nothing there to prevent experienced users from finding those properties, although it can be an imposing task.  I don't put passwords in those, but use them for storing paths to Back-end files and such.
Laila JacksonAuthor Commented:
Excellent dialogue here guys. I have a few more tips to work with now.... Thanks again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.