Avatar of Laila Jackson
Laila Jackson
Flag for Samoa asked on

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
                Forms("frm_user_pin").Controls("txtNewPW").SetFocus
                
                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)
                Forms("frm_user_pin").Repaint
            Else
                '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 ->
                Forms("frm_dashboard").Repaint
            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 ?

Thanks,
Lai
Microsoft AccessMicrosoft ApplicationsVBA

Avatar of undefined
Last Comment
Laila Jackson

8/22/2022 - Mon
SOLUTION
omgang

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Nick67

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Laila Jackson

ASKER
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.
Nick67

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 Jackson

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Laila Jackson

ASKER
Split points as both answers helped me achieve my solution.

Thanks heaps guys. Lai
Nick67

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 Fye

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)

or

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

and you can refer to them with:

Tempvars!AppUser
Tempvars("AppUser")
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:

forms!frmLogin.txt_AppUser

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Dale Fye

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.
Nick67

@Dale
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 Fye

@Nick,

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Laila Jackson

ASKER
Excellent dialogue here guys. I have a few more tips to work with now.... Thanks again.