Public Variable and Forms

[Zymph]

I'm hoping someone can help me with a problem that has been plaguing me for hours now. I do apologize if this belongs in the Forms forum, but I wasn't sure where to put it.

Here we go:

I have a log in scenario the user has to go through which validate their user log-in and typed in password against tblEmployees. If the credentials are good the users ID number is stored in a public variable named lngMyEmpID.

The log-in procedure works well, and lngMyEmpID does store the users ID correctly.

My primary problem is that I need lngMyEmpID to auto populate a USER field on each of my forms so as staff enter information it puts their user stamp on the record. I can not figure out how to make this information automatically appear on the form.

The other thing that I would like to accomplish is that right now lngMyEmpID stores the Employees record number (1,2,3,4) and not their name (Billy, Sue, Mike, Jen). How would I go about doing this so when they enter records it stamps with their Name and not their record number?

Thank you in advance.

 

[The_Doc_Man]

First things first.

If you have a public variable, it is public because other forms will want to see it. (If nobody wants to see it, why public?) But this leads to the question of where it is kept. Normally you might say to put it in the form somewhere. But if you open another form, it cannot be so easily accessed. Not impossible, mind you, but not so easy.

The most likely simple candidate is to have a public module in which that user ID is kept. Here's the catch - the module goes away if all forms go away. It becomes necessary to find a way to keep the public module "alive" - perhaps like the way I do this.

I have a "master control form" that opens up automatically when you launch my DB. The code in the MCF OnLoad event does the login stuff and the validation. If you succeed in login, the MCF stores your user ID in a public variable in a general module. Anything you do, you do through the MCF. It acts as a switchboard and dispatcher for all actions. The MCF never exits until you tell it to close the whole database. That way, the general module remains active and all variables in it stay instantiated.

Warning: Using this approach, you need to trap this form heavily to assure that it never takes an untrapped error. Unhandled error traps deinstantiate the variables and you'll forget who you are.

Back to the other question. If your opening form does your user validation and lookup, it can put anything you need in the public variable in the general module. I do a name lookup and store lots of things in my public variables so they can be displayed later by other forms. In each case, I do that with unbound text boxes that I populate in each form's OnLoad event. You can't do that in the OnOpen event because the form infrastructure isn't ready yet. But OnLoad is soon enough.

 

[delikedi]

I'd like to offer my solution as an alternative.

I use a table that resides in the front-end of the application. I use this table instead of global variables, so the table has as many columns as the global variables it replaces. The table always has just one record. During login, that record is updated to reflect info about the user who is logging in. In my current application, this table has more than ten fields, among which are UserID, UserFullName, UserPwd, UserEmail... etc.

Whenever I need some info from this table, I use a custom function named GV, short for "global variable". This function simply looks up a value from the table. The advantage of using a function is that you can use it both in VBA code and on forms. You can't use global variables on forms.
Whenever I need, say, the user's name from the table, I simply write GV("UserFullName"). This expression is just a few characters longer than the global variable name strUserFullName, and has the advantage of being used on forms.