Storing Variables for later use. (1 Viewer)

[marnieg]

I have a login form for my users. I want to read the user table for some information about the user and store the data retrieved in a variable to then be used on another form. Is this possible?

I read the user table for their default location and I want to use the location on filtering on other forms.

I hope this makes sense.

Thank you,
Marnie

 

[boblarson]

Personally, I use a hidden form which opens when the database opens and I store the username and security level in unbound text boxes on the form for referring to whenever I need.

 

[KenHigg]

One simple way to do this is to do a dlookup() as needed in the application based on the current user. Or you can create global varibles when the database opens and look them up and store them there.

 

[BobMcClellan]

One option is to have a text box on each form that is not visible and stores the username. When user click the submit button, in addtion to opening the target form, update the textbox with the username. Now you can always retrieve info from the usertable for the current user.

Here is an example. App is .adp front end to SQL2005 back end.

Private Sub but_Continue_Click()
If IsNull(Me.txt_UserName) = True Then Exit Sub
If IsNull(Me.txt_UserPassword) = True Then Exit Sub

Dim oCmd As Command, param As Parameter
Dim cn As New ADODB.Connection, sqlString As String

sqlString = "Login_ValidateUser"
Set oCmd = New ADODB.Command
Set cn = CurrentProject.Connection
Set oCmd.ActiveConnection = cn
oCmd.CommandText = sqlString
oCmd.CommandType = adCmdStoredProc
oCmd.CommandTimeout = 15

Set param = New ADODB.Parameter
param.Type = adChar
param.Size = 30 ' 3 bytes
param.Direction = adParamInput
param.Value = Me.txt_UserName
param.name = "un"
oCmd.Parameters.Append param

Set param = New ADODB.Parameter
param.Type = adChar
param.Size = 30 ' 3 bytes
param.Direction = adParamInput
param.Value = Me.txt_UserPassword
param.name = "pwd"
oCmd.Parameters.Append param

Set param = New ADODB.Parameter
param.Type = adInteger
param.Direction = adParamOutput
param.name = "Authorized"
oCmd.Parameters.Append param

oCmd.Execute , , adExecuteNoRecords

If oCmd.Parameters("Authorized") = 1 Then

DoCmd.OpenForm ("DCC_MainMenu"), acNormal
Forms![DCC_MainMenu].txt_CurrentUserName = Me.txt_UserName
Forms![DCC_MainMenu].labVer.Caption = Me.labVer.Caption

End If

End Sub

hth,
..bob

 

[boblarson]

One option is to have a text box on each form that is not visible and stores the username.

Why have a text box on each form that stores the user name? that is excessive. Put it in ONE place (either a hidden form or a global variable, although the hidden form will not lose its value where a global variable can upon an unhandled error) and then refer to it as needed.

I would suggest NOT using a DLookup everytime you need it as that adds uncessessary overhead to the program. Get it once when the program is opened and save it to an accessible location where you can refer to it in code or in queries or in recordsources. The hidden form option is actually simpler at times because you can't use a global variable, for example, directly when using a record source or in a query. You would need a function to get the value from the variable and that can cause undue overhead as well.

 

[KenHigg]

To create a public varible put something like this in a stand alone code module:

Option Compare Database
Option Explicit

Public MyPublicStringVarName As String

And you should be able to store values in it and call on them anywhere in your application. In your case you could use a dlookup to store the user names, etc. in the varibles when the application opens...

 

[BobMcClellan]

Why have a text box on each form that stores the user name? that is excessive. Put it in ONE place (either a hidden form or a global variable, although the hidden form will not lose its value where a global variable can upon an unhandled error) and then refer to it as needed.

I would suggest NOT using a DLookup everytime you need it as that adds uncessessary overhead to the program. Get it once when the program is opened and save it to an accessible location where you can refer to it in code or in queries or in recordsources. The hidden form option is actually simpler at times because you can't use a global variable, for example, directly when using a record source or in a query. You would need a function to get the value from the variable and that can cause undue overhead as well.

Hi Bob,
Thanks for the reply...
I do not use DLookup. I use class modules that send the stored procedure name and parameters to the server. I always use the textbox on the form the submit button is on. I have used hidden forms in the past and would rather have an extra textbox on the one open form than have two forms open.

 

[marnieg]

So I have setup my public variable. On my login screen I read my user table to get the information I want to store in my public variable.

Now I want to reference that public variable in a query for one of my forms.

i.e. MyDefaultLocation is my public variable

In my query on a table I want to only select those records from that table where a field in that table = MyDefaultLocation.

What is the syntax in the query?

 

[KenHigg]

This is where the solution Bob offered, storing the values in a hidden form, is a bit easier but still not what I would do. As he pointed out you need a function to put the value in the query but it's real simple. Something like:

Public Function fncGetValue()
   fncGetValue = myPublicVarName
End Function

Then in the query just use

fncGetValue()

As the parameter.

 

[marnieg]

OK I am a bit confused with the different options given here.

First how do you create a hidden form and should I use this method over the others recommended. Here is the process that I would like to happen

User opens database application
Login form comes up
Type in valid information
Open Hidden Form
Open Switchboard
When user selects an option from the menu the form opens using the saved public variable in the query of the form.

So where does the function calling come in and how does the variable get loaded and passed to the query.

 

[KenHigg]

To start with the primary reason I would not store variables in a hidden form is because it leads to spaghetti logic. I like all public variables in one spot and have one means to access them no matter where in the application you need them. If you put public variable data in a hidden form for one piece of functionality and then somewhere else in another then you have public variables scattered all across your application and have no means of keeping up with them... Hope all of that made sense. Shall I continue or just up and let you use the hidden form method?

 

[marnieg]

Please continue. I didn't understand the hidden form anyway. I have created a module with my Public variable. In my login form I load my public variable. This will be the only place in the application that the global variable is loaded. Now I want to reference it in my queries as explained before. Please continue

 

[KenHigg]

Ok. Give me a minute and I'll create a sample db for you to look at. I see Bob has checked and is currently reponding also...

 

[boblarson]

To start with the primary reason I would not store variables in a hidden form is because it leads to spaghetti logic.

Ken, you can be such a @Q%^!^#$ at times. It isn't spaghetti logic to use a hidden form. In fact, one of the most respected members on this board, and former MVP (Pat Hartman) turned me on to using a hidden form. It is easier in the logic, you don't need to use a function (which gets called for EACH record by the way) in a query, and it is way less overhead. And PUBLIC VARIABLES can lose their values at times in the program. They are volatile, and a hidden form is not.

Shall I continue or just up and let you use the hidden form method?

USE THE HIDDEN FORM METHOD! It is EASIER in referring to a query, requires NO wrapper function, and it will not lose its value unless you restart the program. It is MUCH better than global variables; PERIOD!

 

[boblarson]

OK I am a bit confused with the different options given here.

First how do you create a hidden form and should I use this method over the others recommended. Here is the process that I would like to happen

User opens database application
Login form comes up
Type in valid information
Open Hidden Form
Open Switchboard
When user selects an option from the menu the form opens using the saved public variable in the query of the form.

So where does the function calling come in and how does the variable get loaded and passed to the query.

I have a meeting and then after that I will walk you through it, IF it is okay with SIR KEN.

 

[KenHigg]

Sorry marnieg, Bob isn't big on people skills and I have better things to do than fuss with him. PM me if you want the sample db

 

[marnieg]

So if I use a hidden form I assume I am making a regular form, but with the property of Visible set to "NO". Then how do I reference the data that I want in another form in a query?

I'm open to whatever works best.

 

[KenHigg]

If your application will not be getting very complex then Bob's suggestion should work fine. I'll let him help you when he gets back if you don't mind -

 

[boblarson]

So if I use a hidden form I assume I am making a regular form, but with the property of Visible set to "NO". Then how do I reference the data that I want in another form in a query?

I'm open to whatever works best.

1. Create a form and put two (or more) text boxes on it.

2. Name one of the text boxes - txtUser and the other txtSecLevel

3. Create a Macro with the Action of OPEN FORM and the arguments of:

Form Name: YourHiddenFormNameHere
View: FORM
Filter Name:
Where Condition:
Data Mode:
Window Mode: HIDDEN

Save the macro with the name of AutoExec

Then in your login form when you have the login validated, put code to assign the values to the hidden form:

Forms!YourHiddenFormNameHere.txtUser = Me.YourUserNameHere
Forms!YourHiddenFormNameHere.txtSecLevel = YourSecurityLevelHere
(YourSecurityLevelHere means whatever you have chosen like Admin, Read-Only, Edit-Only, Add-Only, etc.)


Then whenever you need to know the value you just refer to the correct text box (like criteria in a query):

[Forms]![YourHiddenFormNameHere]![txtSecLevel]

That is basically it.

 

[boblarson]

Sorry marnieg, Bob isn't big on people skills

And you are? That's a laugh. I have very good people skills, but I choose to leave them out when dealing with you Ken as you have lost my respect due to things in the past (including your post which you seemed to have either edited or deleted in this thread that happened to say:

If they teach storing variables in hidden forms in MVP school then I'll pass on perusing that credential

So, you want to throw dirt, be prepared to eat some as well.