Declaring Variables

[ineedahelp]

Can someone describe for me with code how I would initialize a variable in a Private Sub, for example, a form's ON OPEN procedure and carry that value throughout ALL my other procedures used with other forms? I used the statement 'PUBLIC mUser as string' in my frmMainMenu. After the user enters her initials, I want mUser to get this value AND I want to use that value again in other forms until the user closes the access application. Thank you for any help!

[MMcCarthy]

You cannot declare a global variable in a private sub

Declare it in a module as:

GLOBAL mUser As String

You can then set it and use it anywhere

Can someone describe for me with code how I would initialize a variable in a Private Sub, for example, a form's ON OPEN procedure and carry that value throughout ALL my other procedures used with other forms? I used the statement 'PUBLIC mUser as string' in my frmMainMenu. After the user enters her initials, I want mUser to get this value AND I want to use that value again in other forms until the user closes the access application. Thank you for any help!

[ineedahelp]

thank you for your help...I am a little confused on the proper way to declare and call for variables in modules. When a user opens my database, I want the program to ask for initials. I use the following code:

mUser = InputBox("Please sign in: ", "User Sign In", , 5000, 5000)
If mUser <> "JG" And mUser <> "jg" And mUser <> "laa" And mUser <> "LAA" Then
MsgBox "Incorrect Entry!", vbCritical
DoCmd.Close acForm, "frmMain"
End If
If mUser = "LAA" Or mUser = "laa" Then
mUser = "Leslie"
Else
If mUser = "JG" Or mUser = "jg" Then
mUser = "Galper@Vodia"
End If
End If

I am guessing that I need this to NOT run in my form, BUT create a SUB in a module. I have done this...

Option Compare Database

Global mUser As String

Public Sub DetermineUser()
mUser = InputBox("Please sign in: ", "User Sign In", , 5000, 5000)
If mUser <> "JG" And mUser <> "jg" And mUser <> "laa" And mUser <> "LAA" Then
MsgBox "Incorrect Entry!", vbCritical
DoCmd.Close acForm, "frmMain"
End If
If mUser = "LAA" Or mUser = "laa" Then
mUser = "Leslie"
Else
If mUser = "JG" Or mUser = "jg" Then
mUser = "Galper@Vodia"
End If
End If
Debug.Print mUser
End Sub

If this is correct, how do I RUN this MODULE to obtain mUser AND how do I retrieve mUser in all the forms that I need it for? Thank you in advance for all your help!!

[MMcCarthy]

This code has to be declared in a module but it can be called from a form. I would change the procedure to a function just to make it easier to call.

In the module:

 
Option Compare Database
 
Global mUser As String
 
Function DetermineUser()
mUser = InputBox("Please sign in: ", "User Sign In", , 5000, 5000)
If mUser <> "JG" And mUser <> "jg" And mUser <> "laa" And mUser <> "LAA" Then
MsgBox "Incorrect Entry!", vbCritical
DoCmd.Close acForm, "frmMain"
End If
If mUser = "LAA" Or mUser = "laa" Then
mUser = "Leslie"
Else
If mUser = "JG" Or mUser = "jg" Then
mUser = "Galper@Vodia"
End If
End If
Debug.Print mUser
End Function
 
 

Have a form set to open on startup of the database. This is usually the switchboard or main menu. In the On Load event of this form put the following.

 
Private Sub Form_OnLoad()
 
   DetermineUser
 
End Sub
 
 

This will run the function which will assign the value to the variable mUser. It will keep this value unless you change it or the database closes and you can reference it anywhere in the database.

[ineedahelp]

Thank you again for your help...this worked perfectly!

[ineedahelp]

One last question I came up with...I don't want the user to have to sign in upon the load of each form. I only want them to sign in ONCE when frmMainMenu opens. I need to tell each form what the value of mUser is. would I do this with the SET command like you had mentioned? what would the code be and where would I declare this variable in each form?

thank you again!...a very slow learner!!

[MMcCarthy]

One last question I came up with...I don't want the user to have to sign in upon the load of each form. I only want them to sign in ONCE when frmMainMenu opens. I need to tell each form what the value of mUser is. would I do this with the SET command like you had mentioned? what would the code be and where would I declare this variable in each form?

thank you again!...a very slow learner!!

The user signs in just once. That value is available anywhere and doesn't have to be declared again. The value will not change as you open and close forms. For instance you can pass the following SQL statement anywhere in code.

"INSERT INTO tblName (UpdatedByField) VALUES (mUser);"

Or set the value on a textbox using code:

Me.textBoxName = mUSer

The only restriction is that it cannot be referenced directly in a query design. But this can be got around by using a function to return it. The following function should be put in a module.

Function getUser() As String

getUser=mUser

End Function

You still can't reference it directly in the where statement but you can get around this by referencing it in the SELECT as follows:

SELECT IIf([UpdatedByField]=getUser(),[UpdatedByField],Null) As tmpA
FROM tblName
WHERE tmpA Is Not Null;

The only way this value will change is if another user signs in or the database closes.