A user logs on to access through a custom log on form. How can I store that username to be used on other forms / reports?

Once they log on I want the log on window to close but I want other forms  / reports to be able to utilize there username? Any thoughts on how to store that information for later?

For instance, the code when they hit submit on the login form would allow them access to the rest of the application and then there username would show in the bottom right of the splash screen.. Just one example.. I also want to use that on reports as well..

Thanks and let me know if you have any more questions..
LVL 2
Jarred MeyerProduction ManagerAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:


Private Sub cmdLogon_Click()

'Check to see if data is entered into the UserName combo box

    If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.cboEmployee.SetFocus
        Exit Sub
    End If

    'Check to see if data is entered into the password box

    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.txtPassword.SetFocus
        Exit Sub
    End If

    'Check value of password in tblEmployees to see if this
    'matches value chosen in combo box

If Me.txtPassword.Value = DLookup("EmpPassword", "tblEmployees", _
            "[EmpName]='" & Me.cboEmployee.Value & "'") Then

        EmpName = Me.cboEmployee.Value
       
        'Close logon form and open splash screen
       
'Verification Complete ...

   Me![UserName] = Me.cboEmployee
   Me.Dirty = False ' save into table



        DoCmd.Close acForm, "frmLogon", acSaveNo
        DoCmd.OpenForm "frmSplash"

    Else
      MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
            "Invalid Entry!"
        Me.txtPassword.SetFocus
    End If

    'If User Enters incorrect password 3 times database will shutdown

    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
      MsgBox "You do not have access to this database.Please contact admin.", _
               vbCritical, "Restricted Access!"
        Application.Quit
    End If




End Sub
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You can use a Global Variable
In a regular VBA Module, in the Declarations section, create the global

Public gvUserName as String

The ... on the login in form, set the global to the logged in name

gvUserName = <the user name>

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
So, I guess ... something like this:


    If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.cboEmployee.SetFocus
        Exit Sub
    End If

   ' user name is good

gvUserName = Me.cboEmployee  ' grab and 'save' the user name

mx
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
defining the Global variable ... see image

mx
Capture1.gif
0
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:

 I use a hidden form myself.  Call it form frmGlobal and have a series of text controls to hold the data I need, such as username, date/time logged in etc.

 It has the added bouns that since it is the first form opened in the app, it's also the first to attempted to be closed by Access, so I can do things like preventing a close from the Access window, or ensuring all other operations are complete, etc.

 I also use it's timer event to check for in-activity, messages from the admin, force shutdowns, etc.

Jim.
0
 
Jarred MeyerProduction ManagerAuthor Commented:
jdettman.. That sounds like that would be a good thing to have.. How would I set a textbox in there to equal the username from the logon? Sorry Database.. I was working on that but was going to have more questions.. But I like where J is going as it sounds like it would solve some other issues I may have later on with storing other things..  At lease maybe make it a little easier?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW, one advantage to that over the global route is you don't loose your values on a reset.  Not critical when running, but during development it makes life easier.

If your going to go the global variable route, I would stuff the data into the table.  Then in your fetch routine if the global is empty, fetch the value from the table.

Jim.
0
 
Jarred MeyerProduction ManagerAuthor Commented:
And how would I set the form to be hidden?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well ... IF I was going to really do this, I would certainly be storing that value in a small local table, which could even be bound to the login form.  Then elsewhere, all you need to do is use DLookup() to check the value.  The advantage of this is ... tables are 'cheaper' than forms, and you can *easily* add more fields, like for password, or whatever.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Thing is ... the Global variable is the most basic, quickest approach.  And in A2010 with TempVars (A2007 also I guess), you don't list the values if you have a reset.

mx
0
 
Jarred MeyerProduction ManagerAuthor Commented:
My Access program won't be too complex or large so the extra space won't be an issue if that's what your referring to.. or Resources..
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

<<How would I set a textbox in there to equal the username from the logon?>>

 Forms![frmGlobal]![txtUserName] =

 Snapshot of some of the things I have in my frmGlobal:

 gobal value form
 It's nice in that it gives you a central point in your app to control things.  Of course it's only one way to do it.

 Some I've seen use a form, but add properties rather then controls to hold the values.  

  Some might consider this a old technique, but I started doing this back with Access 2.0 and never found a good reason to change.  Overall it works well.

Jim.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
SeyerIT:
You already have a Form.  Just create a simple table with a field called UserName, and make that the RecordSource of the Login Form.  

Once you have the login in name verified, you can do this:

    If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.cboEmployee.SetFocus
        Exit Sub
    End If

   ' user name is good

   Me![UserName] = Me.cboEmployee
   Me.Dirty = False ' save into table

  ' done with that ...

To get the UserName anywhere else ...

dim x
x =  DLookup("[UserName", "YourTableName")

x is now set to the saved user name.

mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<And how would I set the form to be hidden? >>

From VBA:

            '  Open the frmGlobal.
270         DoCmd.OpenForm "frmGlobal", , , , A_EDIT, A_HIDDEN

Jim.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"Table Driven Baby!"

That's my motto.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
minor correction to above:

dim x
x =  DLookup("[UserName]", "YourTableName")
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Once you have the login in name verified, you can do this:

    If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.cboEmployee.SetFocus
        Exit Sub
    End If

   ' user name is good

   Me![UserName] = Me.cboEmployee
   Me.Dirty = False ' save into table

  ' done with that ...

Not exactly sure how I incorporate this into my other code?

Private Sub cmdLogon_Click()

'Check to see if data is entered into the UserName combo box

    If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.cboEmployee.SetFocus
        Exit Sub
    End If

    'Check to see if data is entered into the password box

    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.txtPassword.SetFocus
        Exit Sub
    End If

    'Check value of password in tblEmployees to see if this
    'matches value chosen in combo box

If Me.txtPassword.Value = DLookup("EmpPassword", "tblEmployees", _
            "[EmpName]='" & Me.cboEmployee.Value & "'") Then

        EmpName = Me.cboEmployee.Value
        
        'Close logon form and open splash screen
        

        DoCmd.Close acForm, "frmLogon", acSaveNo
        DoCmd.OpenForm "frmSplash"

    Else
      MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
            "Invalid Entry!"
        Me.txtPassword.SetFocus
    End If

    'If User Enters incorrect password 3 times database will shutdown

    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
      MsgBox "You do not have access to this database.Please contact admin.", _
               vbCritical, "Restricted Access!"
        Application.Quit
    End If

End Sub

Open in new window

0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<"Table Driven Baby!">>

  Mine is actually a cross between the two.  I write a record to a current user table, then use a query to bind frmGlobal to that and the main user table.  My login form is a shell and it takes care of writting the current user record, then opens frmGlobal.

  In this way I have per session and per user variables, plus I can use unbound controls on frmGlobal as flags (like the OK to close).  Plus I get all the other things I mentioned above.

  There are any number of ways you can accomplish something like this, but I've found it very handy over the years to have this one controlling form in an application.

  The ability to have a "OnClose" for the app is nice to have (especially when external programs/DLLs need to be stopped or closed down) as is the timer firing once a minute to check for various things.

Jim.

 

0
 
Jarred MeyerProduction ManagerAuthor Commented:
MX, can I just reference the Username field from the table in a textbox? I tried adding a textbox to my splash form and under Control Source I have =[tblUserName]![UserName]

I'm getting an #error! though

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well, I agree with the On Close for app ... for the cases where that is necessary.  

At the upcoming summit, I am pushing BIG for events that handle things at the app level, like OnAppClose, OnAppOpen, etc.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:

just this:
=[UserName]

 ... IF ... you have that table bound to the Splash form. Otherwise, in the Control source of the text box
=DLookup("[UserName]", "YourTableName")

Again, there are many ways to deal with this ... the combo approach as Jim mentioned above, which does have the advantage of controlling App closing ... so, you should probably consider that approach.

mx
0
 
Jarred MeyerProduction ManagerAuthor Commented:
I'm trying the DLookup but am still getting #Error..

Just using [UserName] on another form where I can set the source of the form to the table works good..

My other form is set to a different datasource for some combo boxes already so I can't set it to the tblUserName..

Any thoughts on why that dlookup isn't working?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
post exactly what you have ..

And are you using the hidden form approach with a table also ?

mx
0
 
Jarred MeyerProduction ManagerAuthor Commented:
I fixed it.. I forgot one of the brackets "[".. Thought I double checked it but guess not.. :)

So it all seems to be working well now..

I didn't end up using the hidden form approach.. At least not yet although I may need to by the time I finish this project:)
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Hopefully I got the points distributed fairly enough.. Seems either method here would suffice..
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Works for me ...

thx.mx
0
 
Jarred MeyerProduction ManagerAuthor Commented:
No problem!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.