Newbie back for more; need some authentication help...

[David618]

Hi –

I’m new to programming in Access and I have another challenge. I have this code that runs off a log in form (frmLogIn). Currently it opens an editor window (frmEmployeeData) if the right password is entered (see code snippet below - thanks to help from this forum!). Alternatively, I would like for it to open either the editor window (frmEmployeeData) or a menu form (frmMainMenu) depending upon whether the user who has logged in is designated as a super user (a “True” value in the [Super_User] field next to their employee ID [Emp_ID] in a table [tblEmployeeRoster]). Right now it checks the password in that same table (tbleEmployeeRoster), but now I would like it to open either one of the two forms, depending on whether the value for a different column (Super_User) in that same table (tblEmployeeRoster) is marked with a value of “True” or not.

Can somebody just point me in the right direction as to the logic/ syntax for this? Again, a snippet of the code is below – where I believe this new logic would need to be inserted. As you can see, right now it only opens the editor (frmEmployeeData) after checking the password.

Humbly submitted,
(not worthy) newbie

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

If Me.txtPassword.Value = DLookup("Emp_Pass", "tblEmployeeRoster", _
"[Emp_ID] = '" & Me.cboIdentity.Value & "'") Then


Emp_ID = Me.cboIdentity.Value

'Close the Log In form and open the Editor window

DoCmd.Close acForm, "frmLogIn", acSaveNo
DoCmd.OpenForm "frmEmployeeData"


As noted, instead of opening the form, "frmEmployeeData" I need it to check what type of user this is and open either 1 of 2 different forms.

[ssanfu]

This is AIR CODE!!!

Dim SU as Boolean

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

 If Me.txtPassword = DLookup("Emp_Pass", "tblEmployeeRoster", "[Emp_ID] = '" & Me.cboIdentity & "'") Then
      Emp_ID = Me.cboIdentity.Value
End if
        
SU = FALSE
SU = DLookup("Super_User", "tblEmployeeRoster", "[Emp_ID] = '" & Me.cboIdentity & "'")

'Close the Log In form and open the Editor window
 DoCmd.Close acForm, "frmLogIn"

If SU Then
   DoCmd.OpenForm "frmEmployeeData"
Else
   DoCmd.OpenForm "frmMainMenu"
End if

---------------
Just curious....
How is the "Emp_ID" entered/selected?
If you were to use a combo box, the password and the super user status could be columns in the combo box. You wouldn't have to use the DLookup() function. Just set the column widths of the two columns to zero. They would still be available but not visible.

[David618]

The "Emp_ID" comes from the combo box on the log in form (cboIdentity). It shows the First Name, Last Name and Employee ID, but is bound on the Employee ID (Emp_ID). I included all 3 as visible in case there are 2 employees with the same name, they can distinguish on the basis of the Employee ID (has happened!). Interesting suggestion to use the combo box - maybe 4 variables, and only the first 3 are visible. You think that would be easier/ more efficient?

I'm still having some problems. What's the best way to post code to show you? I recall something about preceeding with "/code" but I wasn't clear about that. Sorry - new to the forum, but want to follow the protocols...

[ssanfu]

What's the best way to post code to show you? I recall something about preceeding with "/code" but I wasn't clear about that.

Click the "Go Advanced" button. At the right of the middle tool bar is the "#". This inserts the tags for pasting on code examples.

The "Emp_ID" comes from the combo box on the log in form (cboIdentity). It shows the First Name, Last Name and Employee ID, but is bound on the Employee ID (Emp_ID). I included all 3 as visible in case there are 2 employees with the same name, they can distinguish on the basis of the Employee ID (has happened!). Interesting suggestion to use the combo box - maybe 4 variables, and only the first 3 are visible. You think that would be easier/ more efficient?

Yes. Why use DLookup(), when you already have the data in the control? Would you post the SQL of combo box "cboIdentity" row source?

It would be something like:

SELECT Emp_ID, Emp_ID & " - " & [LastName] & ", " & [FirstName], [Emp_PW], [SuperUser] FROM tblEmployeeRoster ORDER BY [LastName], [FirstName];

The bound column would be 1
Column count = 4

To get the password, you would use Me.cboIdentity.Column(2) (remember - zero based, so 3rd column is 2)
For SuperUser, use Me.cboIdentity.Column(3)

Note: can't use "Password" as a field name - it is a reserved word in Access.
Here is a list of reserved words: http://allenbrowne.com/AppIssueBadWord.html

[David618]

Thanks, Steve - I'll take a closer look at this. I really appreciate your help!

[David618]

Steve,

Okay, I'm back. I used the combo box and it appears like the SQL statement "Employee ID - Last Name, First Name" (I like that better, BTW). The first (bound) column is Emp_ID, the expression is second, Emp_Pass is third and Super_User is fourth. It now works! Copy of the code is below - thanks again, Steve - you're the man!!!

Very grateful,
David

    'Check the value of the password in tblEmployeeRoster to see if this
    'matches the value chosen in combo box
    If Me.txtPassword.Value = Me.cboIdentity.Column(2) Then
        Emp_ID = Me.cboIdentity.Value
        'Close the Log In form and open the Editor window
        
        If Me.cboIdentity.Column(3) = True Then
            DoCmd.OpenForm "frmMainMenu"
        Else
            DoCmd.OpenForm "frmEmployeeData"
        End If
        DoCmd.Close acForm, "frmLogIn", acSaveNo
    Else
      MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
            "Invalid Entry!"
        Me.txtPassword.SetFocus
    End If

[ssanfu]

Excellent

Question: You have

DoCmd.Close acForm, "frmLogIn", acSaveNo

Why do you have "acSaveNo"?
That argument is used if the design of the form has changed.
You are not changing the design of the log in form. I have never used "acSaveNo" when closing a form.

I just use

DoCmd.Close acForm, "frmLogIn"

"DoCmd.Close" will work also, but I like specifying which form to close.

[David618]

Hi Steve -

Sorry took so long to reply - got sidetracked on another project. To be perfectly honest, I simply "bastardized" the code from somebody else, so that's why. I totally agree with you - no reason to change the design of the form, so I'll take it out.

I just submitted another post that concerns the same Log In form (I also posted all the code for it). It relates to verifying whether the user is active. I forgot to add that and now that I have, the super user authentication no longer works as it should (it doesn't open one form or the other depending on what the user status is = super or not - it opens the same form every time). Basically, the Log In form needs to check: password, whether the user is active or not, and their status (super or not). It will need to save their employee ID in "memory" for use by other objects (namely the Editor will need to pull up the appropriate record for the user who logged in unless it's a super user, that user should have access to all records in the editor).

Sorry I'm muddleing through this piecemeal. For what it's worth, I'm learning quite a bit from you. Thanks again for all of your help!!!

Regards,
David