Login Form with User Level

[mtagliaferri]

I have the following code which is not working as it should...
all users work in different department (currently 3: IBM, OPS and ADM) these values are set in the tblUser under Department depending in which department the user is it will open the appropriate Main Form, Unfortunately it is not working in full, first because I am stuck with the Else option to allow more than 2 options (Currently 3 but the departments might be more)!

Private Sub cmdLogin_Click()
    Dim UserLevel As String
    
    If IsNull(Me.txtUsername) Then
        MsgBox "Enter Username", vbInformation, "Username Required"
        Me.txtUsername.SetFocus
    ElseIf IsNull(Me.txtPassword) Then
        MsgBox "Enter Password", vbInformation, "Password Required"
        Me.txtPassword.SetFocus
    Else
        If (IsNull(DLookup("Username", "tblUser", "Username ='" & Me.txtUsername.Value & "'"))) Or _
        (IsNull(DLookup("Password", "tblUser", "Password='" & Me.txtPassword.Value & "'"))) Then
        MsgBox "Incorrect Username or Password"
    Else
        UserLevel = DLookup("Department", "tblUser", "Username ='" & Me.txtUsername.Value & "'")
        DoCmd.Close
        If UserLevel = IBM Then
        DoCmd.OpenForm "frmMainIBM"
    Else
        DoCmd.OpenForm "frmMainOPS"
    End If
End If
End If
End Sub

 

[pbaldy]

You need quotes:

If UserLevel = "IBM" Then

 

[mtagliaferri]

:banghead: I added the third option and stuck again...

Private Sub cmdLogin_Click()
    Dim UserLevel As String
    
    If IsNull(Me.txtUsername) Then
        MsgBox "Enter Username", vbInformation, "Username Required"
        Me.txtUsername.SetFocus
    ElseIf IsNull(Me.txtPassword) Then
        MsgBox "Enter Password", vbInformation, "Password Required"
        Me.txtPassword.SetFocus
    Else
        If (IsNull(DLookup("Username", "tblUser", "Username ='" & Me.txtUsername.Value & "'"))) Or _
        (IsNull(DLookup("Password", "tblUser", "Password='" & Me.txtPassword.Value & "'"))) Then
        MsgBox "Incorrect Username or Password"
    Else
        UserLevel = DLookup("Department", "tblUser", "Username ='" & Me.txtUsername.Value & "'")
        DoCmd.Close
        If UserLevel = "IBM" Then
        DoCmd.OpenForm "frmMainIBM"
        If UserLevel = "ADM" Then
        DoCmd.OpenForm "frmMainADM"
        If UserLevel = "OPS" Then
        DoCmd.OpenForm "frmMainOPS"
    Else
        
    End If
End If
End If
End Sub

 

[pbaldy]

Your If/Else/End If's are out of whack. I'd use Select/Case for the user level tests.

 

[liddlem]

Hi mtagliaferri
I the number of departments seem to be 'set in stone' . . . for now. But what if there is a reshuffle or the department name changes?
I would think about creating a department table and then doing a simple DLookup.
That way, you don't have to change your code every time there is a department change.

 

[pbaldy]

I would also have a table (sometimes I'm too focused on fixing the code rather than the big picture).

 

[mtagliaferri]

Yes the departments are set in stone and are identified by 3 letter code and are currently 5, hence why I thought that it was a good idea to refer to the 3 letter coding and creating 5 main navigational forms and then creating 5 cases in the VBA.

You recommend to create a separate table for the department and link it to the users with a number as reference (IBM = 1, OPS = 2 ans so on?). I would still need to create cases for the different numbers.

Looking at the next step as in the users table I have a signature field which is usually the initials of Name and Surname, I was thinking that every new record in the various tables that are added by the users will have both the department and the signature on every record as some records might be visible by more departments though a qry.

Or am I getting this all wrong in my brain

 

[pbaldy]

Why would each department have a different form? Normally they'd be in the same table with a department field, and you'd use a single form filtered to the desired department.

 

[liddlem]

Yes the departments are set in stone and are identified by 3 letter code and are currently 5, hence why I thought that it was a good idea to refer to the 3 letter coding and creating 5 main navigational forms and then creating 5 cases in the VBA.

What you are actually saying here is that the departments used to have a 5 character code and they are now changing to a 3-characters code.
So then, they are NOT 'set in stone' FOREVER. My point is that sometime in the future, (maybe even 10 years from now) someone might decide to go back to the 5-character code.

You recommend to create a separate table for the department and link it to the users with a number as reference (IBM = 1, OPS = 2 ans so on?). I would still need to create cases for the different numbers.

In effect, you would have more than one table.
DEPARTMENTS : ID_Dept, DeptCode, Dept_Description
STAFF : ID_Staff, NAME_First, Name_Surname, NetworkName, ID_Dept

So on your staff form, you have a combo box where you can choose what ID_Department the staff member works in.
and the 'NetworkName. is for this ....

Looking at the next step as in the users table I have a signature field which is usually the initials of Name and Surname, I was thinking that every new record in the various tables that are added by the users will have both the department and the signature on every record as some records might be visible by more departments though a qry.

On EVERY table, I add 2 fields called 'UPDATE_BY' and 'Last_Updated' and
On EVERY form, I add the following code to the 'BEFORE UPDATE event.

me.UPDATED_BY = environ("Username")
me.Last_Updated = now()

The 'environ("Username")' will pick up the name of the person who changed the record.
Now you can use the UPDATED_BY field to find the user in the STAFF table, by looking up the NetworkName.

 

[mtagliaferri]

Sorry, I think I did not phrase it correctly above, the departments are currently 5, little chance that they will increase but that is not set in stone, what is set in stone is the code of the department which will always be mad of 3 letters, hence me thinking that I could use this 3 letters as an indication of the security level.
To get this right, the combo box that reads from the DEPARTMENTS table will write the ID_Dept in the table users, is this correct? And I will have 5 levels of access (1, 2,3, 4, and 5)?
Sorry to be that dull:banghead:

 

[isladogs]

Ok to clarify.
You currently have 5 departments each with a 3 letter code and different user levels of security.
You want to have a separate form for each department for some reason

The advice given by Liddlem & pbaldy still applies.

However unless the content of each form is totally different to the others, a more logical approach is to use the same form for each department but have more/less controls visible or perhaps different controls visible.

E.g if the security levels go from 1 to 5 with level 5 being highest level for e.g. admins then level 1 only sees a few controls, level 2 sees some more, ... and level 5 sees them all.

You could also have certain controls locked for level 1 but unlocked for level 2 etc

You may find this sample database post relevant here
Set a group of controls as visible/hidden, enabled/disabled, locked/unlocked

 

[mtagliaferri]

this will keep me busy over the weekend
Thanks everyone for the help!!

 

[constableparks]

On EVERY table, I add 2 fields called 'UPDATE_BY' and 'Last_Updated' and
On EVERY form, I add the following code to the 'BEFORE UPDATE event.

me.UPDATED_BY = environ("Username")
me.Last_Updated = now()

The 'environ("Username")' will pick up the name of the person who changed the record.
Now you can use the UPDATED_BY field to find the user in the STAFF table, by looking up the NetworkName.

Thanks, liddlem, for this little gem! I didn't realize that I needed it until I saw your post. Your are correct - eventually some data will get changed and my boss will want to know who updated it and when.