Full Version: Making 'Public Variables' available to all forms, queries, &
UtterAccess Forums > Microsoft® Access > Access Forms
How can I define, declare, and initialize a static, public variable at the very beginning of my database startup so that what the data the user initials delivered after a msgbox prompt can be used throughout the entire database.
I'm making timesheets in Access for the whole office. Its easy to make a form with lots of subforms in it to sort out ONE person's leave, work time, and totals... but I don't want to have to make a separate set of forms and reports for each person in the office just to replace the person's initials... BAF, DEV, etc. I want them to supply their initials once and have the database use those initials in all kinds of places after that.
I've tried setting up a Public Variable in the General Declarations, linking that variable to a macro-type command such as Open_Form frmSplashScreen, and setting up a InputBox function that prompts the user for their initials... but I can't get that variable's information out of the procedure for use in a form or report somewhere else.
Welcome to Utter Access!!!
One thing you could do is create a public function that returns the value of the Public variable. The Function can be used in SQL statements, Control Sources, Default values, etc. -- when the variable itself cannot be.
For example:
Public gstrInitials as String

Public Function GetInit() as String
   GenInit = gstrInitials
End Function

hope this helps
I would do this a differently assuming that this is a multi user app accessed over a network. What i would do is use the code found here to capture the user's network login and use that instead to filter your forms. If you really need to use initials, then create a Users table with the login ID as the primary key and initials as a field. You can then use a DLookup to pull the initials from that table.
lso, since this may be sensitive data, you might want to include a password as part of the Users table to make sure someone doesn't sit at someone else's PC to access the application.
An easier way to do this would be to have the person log their initials into a form at startup then when they click "OK" or "Login" or whatever your button says, just hide the form instead of closing it. You can then just reference the text box on the form instead of relying on variables. The problem with using variables is that if any of your code breaks in the database then the variables lose their values.

Private Sub cmdLogin_Click()
If not IsNull(Me.txtInitial) Then
    Me.Visible = False
    MsgBox "Please enter your initials"
End If
End Sub

You can reference the form's text box from other forms or modules by the code below (I am going to assume the name of your form is "frmLogin" and the name of the initials text box is "txtInitial"


Edited by: uno1980 on Tue Oct 5 9:36:37 EDT 2004.
Here's what I've found after typing up code like that from books... I can't pull the GenInit results or the gstrInitials values out of that function. What is the syntax in the form or query for using that value? Can I simply assign an unbound text box in the splash screen (at the beginning of the database opening... after the MsgBox gets the data) to the value supplied for gstrInitials as: (in the Properties Box)
Control Source "=[gstrInitials]"
For just
Control Source "gstrInitials"
what do you think?
Thanks, this and all the other replies have been really helpful.
I'll let you know how it goes.
Glad to assist.
I've been trying some of your code and I still can't get the values to come over to the various forms from the code functions. I get the error "#name?" in the text boxes and other locations that I'm trying to use the "txtInitial"... which usually means there is some kind of data type mismatch right?
like the hidden form idea and I'll probably go with it, but I am not able to access the variables yet.
the #Name error can mean a variety of things, most likely it is a control source property error on a text box or combo box somewhere. Can you attach the database in zipped format for me to take a look, please provide a description of what you are trying to do.
Here it is...
I'm trying to:
1. create an open-on-startup InputBox/MsgBox that can receive User's Initials and save as Public Variable
2. show the user's initials on the frmSplash
3. use the Public Variable to filter the frmTimeSheet for only that specific user
4. a search of the code would reveal that my initials were hand entered about 10 times to get the form to do what I wanted it to do
thanks again!
Since your tables are linked I am not able to test this. I altered the Login Form as well as some of your labels on the time sheet forms. Look in the code windows for the changes. I also altered your main sub form your were trying to filter. The recordsource is changed during the load event of the subform to dynamically filter the form. Again I hope this all works, as I was unable to test. From the little I could tell about your table structure, It looks as though your system is very un-normalized. You should address this problem before going any further. It will require quite a bit of work up front but will pay off in the long run making your database easier to maintain in the future. There is lots of good info about normalization in the Access FAQA Forum on this site.
THere is an index of lots of good discussions to get your started
Sorry, here is the attachment
I'll be digesting this for a while.
Hey Brian,
Your stuff worked... after a little tweeking. I blew away the line you said might cause trouble and I reconciled some naming problems with linked tables.
How I have:
1. a login form that works (with >L>L>L caps input assurance!!)
2. a splash screen that labels who's initials were entered on the login
3. a timesheet form that also labels who's initials were entered on the login PLUS...
4. the first subform (main time data) SQL links correctly to the txtInitial
Thanks! ... but I think I need two more things before releasing this into the WILD next week:
1. the totalling subforms on the bottom need to link to the [Forms]![frmLogin]![txtInitial] "variable"
2. the last subform needs to disappear if the txtInitial is not either "GJB" or "BHF"
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.