Public Variables ain't so public

[The_Doc_Man]

I'm either having a brain cramp or something isn't as obvious as it seems.

Scenario: I have a DB (Ac 2k3) that will do some departmental workload tracking across a large number of machines requiring work. I'm rolling my own security inside the forms and hiding the DB window. Each user in my list of authorized users will have one of four roles, each with increasing access rights vis-a-vis the previous one.

The DB has a startup form that is a switchboard. It is supposed to stay open for the lifetime of the session, and you close the session with a button on the form. The startup form is responsible for defining the security. I have some auditing defined for this process, too. The switchboard logs things like opening and closing forms, but each individual form is responsible for at least some parts of its own security.

I'm in a domain-based environment with strict Windows security, so there is a user login name available from domain-based information. In the startup form, I use the Environment("Username") construct to look up usernames. I get the one I want and that lookup works OK. It is the correct username for the domain, and that username is of course unique across the domain.

I do a quick recordset operation to select the Username index and find that username (or classify the user as a guest if no record). If the user is known, I look up the user's role field. This will be a simple number that defines just how much of the system each user can use - disabled, guest, staff, DBA. Don't need more complex than that.

OK, so the theory is that when I open a form from the switchboard, the switchboard is still open, just "not on top." Any public variables from the switchboard should still be instantiated. But I can't find them using any syntax and logic available to me.

More specifically, the Form_OnLoad routine in the second form cannot see the public variables in the startup form, variables which it would use to decide what this user can do. So I researched the various ways to do this and I'm coming up blanker than I should. I'm obviously missing something, but it isn't clear as to what.

In this situation, I have two forms open, A (the switchboard) and B (the form opened via switchboard).

In the class module for A, I have a couple of public variables, call them SAPK (a person tracking number) and AccRights (the role number), both LONG.

First blush, VBA code in B in the the _OnLoad event should be able to see both of these because A is still open. But no, it cannot.

Second blush, searching the forum, I found what DCrake says will work, where you create a public function to get the public variable. Define the public functions in the class module of A. Can't see the functions when I do that.

Third blush, moved the variables to a general module. Had Form A load the variables. Stepping through the debugger, the values are defined. Still cannot see the public variables from VBA in form B.

Fourth blush, used the GetSAPK and GetAccRights public functions from the general module instead of trying direct access. Still no joy.

I thought about creating a class object, then populating it with the info with property let and property get stuff, but that still leaves me with the question of finding the class object from VBA in class module of B if I created the new object in the class module of A (or, apparently, the general module). I'm in the same boat except now it is finding the class object instead of finding the variables in the class module.

The symptom seems to be that I cannot find the item I seek, whether it is the SAPK value or the AccRights value. No combination of syntax seems to work. No, there are no name overlaps in the modules.

If I use syntax based on Form_OpeningDB, it seems to know the names of the values, but when I run the VBA code, it can't resolve the names even though the form in question is open. I've tried variants of Forms!OpeningDB and (using the collection syntax) Forms("OpeningDB") - but nothing seems to fly here.

I downloaded and examined DCrake's example, and I'm damned if I see what I'm doing different. All I want is to see that furshlugginer LONG variable defined in form A from form B. So, any thoughts?

 

[Banana]

Let's see if I'm following you.

The public variables originally were defined in the module behind the switchboard form, no?

You used the syntax like this:

Form_MySwitchBoard.MyPublicVariable

OR

Forms!MySwitchBoard!MyPublicVariable


and it didn't work?


You then moved the public variables to a general module and declared it as

Public MyVariable As SomeType

and you made sure the switchboard form assigned some value to MyVariable, but the form B is unable to read the variable.

Is that correct?

If so, then my suggestion would be to make a new blank database, remembering to turn off AutoCorrect (AutoCorrupt) and import in all objects then try again. Does it persist?

 

[SOS]

If you want truly public variables (accessible by anything, anywhere) with no need to refer to the object they were declared on, you declare them in the General Declarations Section of a Standard Module.

 

[The_Doc_Man]

I'm either having a brain cramp or something isn't as obvious as it seems.

Turns out it was "brain cramp." After slowing myself down a bit, taking the proverbial deep breath, and a couple of other things, I finally got it to make sense.

First, SOS is right about General Modules. Just as a precaution, I implemented functions to get and subroutines to put the state variables I wanted. As long as the startup form is hot, so is the general module, and that is the way I thought it was.

But I forgot one little detail.

If you have the Startup Form open (minimized), but go into Design Mode on the OTHER form AND click the Code icon, you lose EVERYTHING in your context. you have to close and reopen the opening form to regain the state variables I wanted to keep.

I had that option turned off about "Doing this will reset your project" so I wasn't getting the reminder. It's turned on again so while I'll be annoyed by yet another message box, at least I'll know WHY I'm being annoyed. And why I WANT to be annoyed in this particular case.

One big DUH for the Doc Man!

 

[Banana]

That's quite annoyed, those amnesia bout of Access, isn't it?

I don't know if it's a option in your case, but one thing i've took to is to do a self-healing routine so I never need to worry about the amnesia attack.

For example, if I want to reuse a object several time:

Public Function conn() As ADODB.Connection

Static c As ADODB.Connection

If c Is Nothing Then
   <initialize everything>
End If

Set conn = c

End Function

The above code won't re-initalize every time it's called, only if it has a nothing value for the object so the performance difference is sure to be neligible, and I'd instance we could adapt this for a API call to get the username:

Public Function WhoAmI() As String

Static s As String

If s = "" Then
   <Do the API Call>
End If

WhoAmI = s

End Function

Hope that helps.