By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,641 Members | 2,085 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,641 IT Pros & Developers. It's quick & easy.

Declaring Variables

P: 98
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!
Oct 20 '06 #1
Share this Question
Share on Google+
6 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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!
Oct 20 '06 #2

P: 98
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!!
Oct 23 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3.  
  4. Global mUser As String
  5.  
  6. Function DetermineUser()
  7. mUser = InputBox("Please sign in: ", "User Sign In", , 5000, 5000)
  8. If mUser <> "JG" And mUser <> "jg" And mUser <> "laa" And mUser <> "LAA" Then
  9. MsgBox "Incorrect Entry!", vbCritical
  10. DoCmd.Close acForm, "frmMain"
  11. End If
  12. If mUser = "LAA" Or mUser = "laa" Then
  13. mUser = "Leslie"
  14. Else
  15. If mUser = "JG" Or mUser = "jg" Then
  16. mUser = "Galper@Vodia"
  17. End If
  18. End If
  19. Debug.Print mUser
  20. End Function
  21.  
  22.  
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_OnLoad()
  3.  
  4.    DetermineUser
  5.  
  6. End Sub
  7.  
  8.  
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.
Oct 23 '06 #4

P: 98
Thank you again for your help...this worked perfectly!
Oct 23 '06 #5

P: 98
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!!
Oct 24 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 25 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.