Login User Table

Question

I currently use login code (see below) in the After Update property of the password field of the login form.  In my user table I have a field titled Employee Entry ID that I would like to be used in other screens and queries to limit data to only this user's data.  Is there a way to set this as an active field to be a usable variable in other queries as long as the user is logged in?

Option Compare Database

Option Explicit

Private Sub txtPassword_AfterUpdate()

'Check that User is selected

If IsNull(Me.cboUser) Then

   MsgBox "You need to select a user!", vbCritical

   Me.cboUser.SetFocus

Else

   'Check for correct password

   If Me.txtPassword = Me.cboUser.Column(2) Then

       'Check if password needs to be reset

       If Me.cboUser.Column(3) Then

           DoCmd.OpenForm "frmPasswordChange", , , "[UserID] = " & Me.cboUser

       End If

       DoCmd.OpenForm "frmMainMenu"

       Me.Visible = False

   Else

       MsgBox "Password does not match, please re-enter!", vbExclamation

       Me.txtPassword = Null

       Me.txtPassword.SetFocus

   End If

End If

End Sub

Answer 1

Hi Terry,

For #1, you got me... I actually didn't take a look at your code earlier and just noticed that you're already hiding the form by using this line:

Me.Visible = False

That hides the form and you can simply reference it by using the Forms!FormName.ControlName syntax

For #2, take a look at this Wiki article: TempVars

Hope that helps...

Answer 2

This looks like the code from my blog. If it is, my blog tells you that you can use:

Forms!frmLogin!cboUser

to retrieve the UserID. That's why the form is hidden so that it remains open so the control (cboUser) can be referenced anywhere in the app.

Answer 3

Thank you DBGuy!

Answer 4

Thanks!  I do appreciate the guidance, it helps a lot by steering me in the right direction.  For option 1, keeping the login form open, what line of code would you add to the VB code listed above to keep the form open but hidden?  I know how to do this with a macro, but not sure on  the VB code.

For option 2, do you have a sample line of code to create a temp variable?

Thanks in advance for all your help.

Answer 5

Hi. You have at least two options:

1. Keep the login form open but hidden and use form reference in your queries, or
2. Use TempVars to store the info so you can use it anywhere in your database.

Just a thought...