Global Variables

[kit_sune]

Good Day/Night all,

I've learned so much from this forum so far and for that I am grateful. My Tech Support tracker Database is coming together great - I'm very excited to give my coworkers an update on it soon.

Before I get to that point though I have one more function that I absolutely need, and I'm thinking it requres the use of a kind of Global Variable.

I have a login screen that each of my users process through. The goal of this is not actually Database Security, it's Form management - I want to tailor the experience of each of my users depending on who they are and what section they belong to.

The question:

I have a table with everyone's names, and section they belong to (and other values).

When a user has successfully kicked off the login code I need to remember who they are so that future forms they view show only specific values that are related to the section they work in.

Here's the code I have currently - all it does is display a simple message.

Private Sub Login_Button_Click()
Dim myUser
myUser = User_Text.Value
If Me.Password_Text = Nz(DLookup("Password", "Personnel_Table", "Name_MMA= '" & myUser & "'"), "N/A") Then
   MsgBox "Yay it works.  Now go celebrate."
End If
End Sub

Also, once a global variable is established, how would I read it from another form?

Thanks,

~Kit

 

[pbaldy]

One simple option is to hide the login form rather than close it, which lets you refer to the user from anywhere in the db by referring to the textbox.

 

[curscascis]

I am no expert, but updating a table with those values and holding them there till the application is closed could also help. Another more difficult solution is to export the data to a temporary file and access the information whenever needed, and later the file could be erased using KILL i believe. Hiding the form sounds like the most promising way though.

 

[kit_sune]

Sure that does sound promising. Could I could refer to the variable "myUser" that I created in the subform which refered to the User_Text text box, or would I need to refer to the text box itself?

Thanks!

 

[pbaldy]

Not as you have it, as the variable is declared within the procedure. If you want to go the global variable route, declare the variable in a standard module. You would also need a function to be able to get its value outside VBA.

 

[curscascis]

Don't forget to either make the variable Public or make the module Explicit when declaring it another module.

 

[kit_sune]

Perfect, it is working great.

I made a Global variable with the following:

[COLOR=black][FONT=Verdana]Option Compare Database[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]Public theUser As Variant[/FONT][/COLOR]

During the login process I have a listbox with the users in it change the User_Text value, then upon clicking Login it changes the global variable to match what the text box had stated upon login.

[COLOR=black][FONT=Verdana]Private Sub Login_Button_Click()[/FONT][/COLOR]
 
[FONT=Verdana][COLOR=black]theUser = User_Text.Value[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]If Me.Password_Text = Nz(DLookup("Password", "Personnel_Table", "Name_MMA= '" & theUser & "'"), "N/A") Then[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  MsgBox "Yay it works.  Now go celebrate."[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]End If[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]End Sub [/COLOR][/FONT]

Thanks everyone. I have not yet referred to this variable in any other forms but I don’t think I’ll be running into any problems from this point.
Thanks again!

~Kit

 

[missinglinq]

You don't say (I don't believe) what version of Access you're using, but there is one caveat you should consider, here, when using Global Variables; if an error is raised, the Value of all Global Variables are lost, so keep that in mind!

If you're running Access 2007 or later, there is a new goody called a TempVars. These act in much the way as Global Variables, but don't lose their Value when errors pop!

To assign a Value, here's an example:

 TempVars.Add "strAppName", "Test Application"

where 'strAppName' is the name of the TempVars and 'Test Application' is the value being assigned to it.

To use the Values

 MsgBox TempVars!strAppName

will display it.

To Remove a TempVar

TempVars.Remove "strAppName"

Linq ;0)>

 

[curscascis]

Haha, TempVars are good indeed. MissingLinq, it seems as though you were the missing link in this problem.

 

[kit_sune]

To assign a Value, here's an example:

 TempVars.Add "strAppName", "Test Application"

where 'strAppName' is the name of the TempVars and 'Test Application' is the value being assigned to it.

This sounds pretty good. Can I create a TempVar anywhere, and it would be recognized throughout all the forms, even when the current form was closed, or do I need to follow the same rules as a Global Variable?

Thanks,

 

[missinglinq]

It can be created anywhere and remains in effect until the app, itself, is closed.

Note that it will pop an error if you attempt to use it before it's been created! In other words, if

1. In FormA you have TempVars.Add "strAppName", "Test Application"
2. In FormB you execute the code Me.ProgramName = TempVars!strAppName
3. You close your app then re-open it
4. Run FormB before running FormA

you'll pop an error. It has to be created and assigned a value before attempting to use it, which makes sense, of course!

BTW, the same rules apply for Global Variables; once assigned a value, the value holds even after the Form it was assigned in closes, until the app, itself, closes, barring, as mentioned before, an error occurring.

Linq ;0)>

 

[ChrisO]

Global variables are not lost on error under any circumstance.

If an unhandled error occurs in an MDB or ACCDB file we can enter debug mode and the global variable still has its value. The error has occurred but the global variable has maintained its value.

Furthermore, if global variables lost their value then the global error object would also lose its values and therefore the error number would be 0 and the error description would be a zero length string under all circumstances. That would be an absurd situation.

Global variables are re-initialised if a Reset occurs in an MDB or ACCDB file. A Reset occurs after the error has occurred and after we leave debug mode. The Reset also occurs if we choose End and do not go into debug mode.

None of the above happens in an MDE or ACCDE file.

----------

Global variables are also re-initialised if a DoCmd.Quit or Application.Quit is executed anywhere within the application. That re-initialisation occurs before any Forms or Reports are required to close. Therefore, any Close or Unload events will run with re-initialised global variables if a Quit has been executed. Also, any recordsets are closed because their variable pointers are set to Nothing.

The above happens in both MDB/ACCDB and MDE/ACCDE files.

TempVars should be checked for this as well. It would be consistent of Microsoft if TempVars were re-initialised on a VBA Quit command. If TempVars are re-initialised then they are no better than global variables in this regard.

----------

But global variables are not lost on error under any circumstance. It is just a fallacy which gets propagated on the web.

Chris.

 

[ChrisO]

Nobody seems to want to have a crack at it so here is a result.

We could also use the database properties but that might be too much work for some…

Option Explicit
Option Compare Text

Public con As New clsCon


Sub TestIt()
    Dim strPropName As String
    Dim strPropText As String
    Dim vntReturn   As Variant

    strPropName = "Some Name"
    strPropText = "Some Text"
    
    If SetProperty(strPropName, con.DAO.dbText, strPropText) = con.DAO.PropertyErrorSet Then
        MsgBox "Failure to set the database property '" & strPropName & "'"
    Else
        vntReturn = GetProperty("Some Name")
        
        If vntReturn = con.DAO.PropertyErrorGet Then
            MsgBox "Failure to get the database property."
        Else
            MsgBox vntReturn
        End If
    End If

End Sub


Public Function SetProperty(ByVal PropName As String, _
                            ByVal PropType As Long, _
                            ByVal PropValue As Variant) As Variant
    
    On Error Resume Next
        With CurrentDb()
            [color=green]' Attempt to create the property and assign its value.[/color]
            .Properties.Append .CreateProperty(PropName, PropType, PropValue)
            .Properties(PropName) = PropValue
            
            [color=green]' Return the verification of the creation and assignment.[/color]
            If .Properties(PropName) = PropValue Then
                [color=green]' If set correctly then return True.[/color]
                SetProperty = True
            Else
                [color=green]' If not set correctly then return default error message.[/color]
                SetProperty = con.DAO.PropertyErrorSet
            End If
        End With
    Err.Clear

End Function


Public Function GetProperty(ByVal PropName As Variant) As Variant

    On Error Resume Next
        [color=green]' Attempt to get the database property.[/color]
        GetProperty = CurrentDb.Properties(PropName)
        
        [color=green] If error then return default error message.[/color]
        If Err.Number Then GetProperty = con.DAO.PropertyErrorGet
    Err.Clear

End Function

The above code survives a project Reset and a VBA Quit command.

Late bound demo attached, in version Access2003, and it’s not what you may have seen on the web so far.

Chris.

 

[The_Doc_Man]

Regarding the issue of how we know who is logged in:

I put an unbound text box on my form. In the form's OnLoad event, I look up the user by getting an environmental variable called "Username" - which works for us because we are in a domain-base login environment. Windows puts the name of the logged in person in the environmental variable where it can be queried. I look up the person's login name, which is unique across the domain, in my user properties table. I put the user's "Display" name in the unbound text box. I put their role name in another box.

The form in question is used for dispatching functions, so when a user is in my database, that master control form never dies, it just gets hidden by other forms that overlay it. That means I can use the syntax for pulling something off of another form.

ChrisO's comment about global variables retaining their values across traps that were handled by Access itself is one that I cannot confirm. Let me be absolutely clear on this. I am not calling ChrisO a liar. I will merely state that I believe the situation is complex enough to have complex sensitivity to context in regards to global variable survival. My experience and his do not match on this topic.

However, I am equally sure that ChrisO and I would agree that it is a bad practice to not do your own trap handling on all of your forms so that you never have to find out whether your global variable survived the trap. Therefore, if you are going to use global variables, do your best to put good trap handlers in your class modules and global modules.

 

[ChrisO]

Doc.

In reality I do not mind being called a liar, if you did or not say so.

What I do mind are posts which state fussy facts and your last post seems to be full of them.

Some of the wording in your reply is bordering on the ridiculous. “Complex sensitivity to context” what a load of crap.Do you really think that people are so stupid that you can state such a thing? Do you think I am so stupid as to accept such a thing?

It is an insult to any thinking person.

Can you prove, via a demonstration database, anything you have said?

Not words here but proof in a verifiable manner.

Chris.

 

[Galaxiom]

Readers might also be interested in this thread which discussed the same subject earlier this year.

It gets going toward the bottom of the first page.

I note The Doc Man continues to disagree with ChrisO yet provides not a shred of evidence to back his case. Hearsay is not a valid argument.

It is not a complex situation. When the code breaks it stops. When it stops nothing further happens. The variables do not lose their values because there is nothing to cause that to happen. End of story.

Variables are cleared by the reset that often follows the break. The difference between Break and Reset is important but confused by too many who unfortunately continue to promulgate the myth and expose their ignorance.

 

[ChrisO]

Galaxiom.

They are all bubble and froth; they couldn’t understand it if it bit them on the arse.

For them it’s all repeat, repeat, repeat with no understanding. They have no idea what they are saying.

In the short term they might get offended and complain or simply run away.
In the long term they might get educated and repeat their new found knowledge.

That is the way things go…

Chris.

 

[The_Doc_Man]

OK, guys. Let's not get to flaming.

My experience does not appear to match yours. I do not currently have a older copy of Access on which I can demonstrate the problem because of site upgrades and project changes. I acknowledge that product changes can occur over time as MS releases new copies of Office.

I do not back down from the statement but at this time I cannot demonstrate code examples. I advised a very specific type of precaution to the OP that is merely good programming practise in any situation. If you can't see that much, then you are blinded by something I don't understand.

I am what I am. I've been programming for 40+ years and know my way around bit buckets. I know the difference between universal, global, and local variables and between general and class modules in various language.

I have seen things in Access that at the time indicated that variables were lost after an improperly handled trap. I acknowledge that my clumsiness led to the mishandled trap. I guess I don't care if you wish to malign my analysis skills, but I certain didn't intend to malign anyone. Stating that "I have observed other behavior" is simple enough. If you disagree, fine. Live with the fact that I still think I saw what I saw when I saw it.

 

[ChrisO]

Doc.

This may be where we differ…

I do not believe it is sufficient to say "I have observed other behaviour" and then go on to make suggestions based on that observation. I test things and there are times I don’t believe the results and so I test them again. There are also times when I ask someone else to test them as well.

I have never seen any variable, global or not, unexpectedly lose its value which was not under program control.

Here is another fact which people can verify by doing a Google search. Very few people know that a VBA Quit command resets Global variables.

Yet how many people create a quit command button to shutdown Access? One day they run into trouble and come to a site like this. But people don’t know about the Quit command and start jumping to the wrong conclusion that global variables have simply lost their value. How long would it take for a program to crash if the contents of memory simply evaporated into thin air?

We can not get to the correct result by jumping to the wrong conclusion.

And there are a lot of people who won’t go and test things. Too busy, don’t know how to test, whatever the reason they do not take the time to test things. They will, however, take plenty of time to defend their position for reasons best known to them.

So, to me, it’s one thing to observe something but it’s a completely different thing to assume that what we have observed is correct. It is even worse to take that observation and pass it on to others.

That is what I complain about; the passing on of ‘observations’ and not provable facts.

Chris.

 

[Galaxiom]

I have seen things in Access that at the time indicated that variables were lost after an improperly handled trap.

And what happened after the error that stopped the execution?

The VBA Continue/End/Debug dialog popped up. You pressed End which Reset the variables. The error itself did not reset anything as can easily be seen in the Locals window. How would debugging be if VBA destroyed the information you needed to debug?

Moreover, in VBA Options under the General tab there is a section called "Edit and Continue" which contains a setting "Notify Before State Loss". If it is checked VBA will raise a warning when you press End.

What do you think "State Loss" means? And why does it offer to notify? Because it gives you the choice to retain your variables or lose them. VBA does not kill your variables arbitrarily.

If instead you went into the code and commented out the problem then pressed Continue the program would resume and the variables would have remained intact and you would not receive the State Loss warning.

I do exactly this while debugging and I have never experienced any variables losing their values.