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?

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

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

defining the Global variable ... see image

mx
Capture1.gif

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?

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.

And how would I set the form to be hidden?

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

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

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..

<<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:

 
 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.

<<And how would I set the form to be hidden? >>

From VBA:

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

Jim.

"Table Driven Baby!"

That's my motto.

mx

minor correction to above:

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

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

Select allOpen in new window

<<"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.

 

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

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

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

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?

post exactly what you have ..

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

mx

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:)

Hopefully I got the points distributed fairly enough.. Seems either method here would suffice..

Works for me ...

thx.mx

No problem!