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.
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.
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
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 StringWhen 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 IfMy 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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
@ 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.
ASKER
Split points as both answers helped me achieve my solution.
Thanks heaps guys. Lai
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.
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.
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.
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.
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.
@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("Cust
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.
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("Cust
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.
@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.
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.
ASKER
Excellent dialogue here guys. I have a few more tips to work with now.... Thanks again.
ASKER
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.