Access Levels: having different views for different logins

[Tej21]

Hi all,

Im creating a database in access for a fictional sports centre. What i would like help on is having different views for different logins. So, if i typed in the managers username and password, i would be able to see ALL buttons, if i typed in an admin accounts username and password, i would only be able to see some buttons and the others have disappeared.

I know how to set things so they cant be seen:

........Visible = False or True......

but cant seem to attached it to users.

Any help?

Thanks.

Tej

[jaxjagfan]

Hi all,

Im creating a database in access for a fictional sports centre. What i would like help on is having different views for different logins. So, if i typed in the managers username and password, i would be able to see ALL buttons, if i typed in an admin accounts username and password, i would only be able to see some buttons and the others have disappeared.

I know how to set things so they cant be seen:

........Visible = False or True......

but cant seem to attached it to users.

Any help?

Thanks.

Tej

You will need a user table and a user group table. Each user will belong to a group you will establish for your app.

tblUser
UserID, Login, Name, GroupID, Password (Set format of password field to "Password" so it is displayed as "******"). Login will be their user login (IE rrabbit)

tblUserGroup
GroupID, Group

A module with a Public variable - Public iGrp as Integer.

Unbound Form - frmLogin. First form that displays in app. Has 2 textboxes (txtLogin, txtPass) and 2 command buttons(cmdOK, cmdCancel). When User enters Login, Password and Clicks Ok you will need a DLookup to ensure correct password in tblUser.

When correct password is acceptable then a second DLookup to get GroupID
from tblUser.

Private Sub cmdLogin_Click()
If Me.Pass <> Dlookup("[Password]","tblUser","[Login] ='" & Me.txtLogin & "'") Then
Msgbox "Incorrect Password - Try Again!"
Me.txtPass.SetFocus
Exit Sub
Else
iGrp = Dlookup("[GroupID]","tblUser","[Login] ='" & Me.txtLogin & "'")
Docmd.OpenForm "YourMenuForm"
End Sub

In the menu form you will need a function or Case statement to control what gets turned on or off according to iGrp.

Select Case iGrp
Case 1
Me.cmdOpenMgr.visible = False
Case 2
Me.cmdOpenMgr.visible = True
End Select

I have apps which get the Windows UserID and then looks up the group and goes from there. No login form required.

Hope this helps!

[Tej21]

You will need a user table and a user group table. Each user will belong to a group you will establish for your app.

tblUser
UserID, Login, Name, GroupID, Password (Set format of password field to "Password" so it is displayed as "******"). Login will be their user login (IE rrabbit)

tblUserGroup
GroupID, Group

A module with a Public variable - Public iGrp as Integer.

Unbound Form - frmLogin. First form that displays in app. Has 2 textboxes (txtLogin, txtPass) and 2 command buttons(cmdOK, cmdCancel). When User enters Login, Password and Clicks Ok you will need a DLookup to ensure correct password in tblUser.

When correct password is acceptable then a second DLookup to get GroupID
from tblUser.

Private Sub cmdLogin_Click()
If Me.Pass <> Dlookup("[Password]","tblUser","[Login] ='" & Me.txtLogin & "'") Then
Msgbox "Incorrect Password - Try Again!"
Me.txtPass.SetFocus
Exit Sub
Else
iGrp = Dlookup("[GroupID]","tblUser","[Login] ='" & Me.txtLogin & "'")
Docmd.OpenForm "YourMenuForm"
End Sub

In the menu form you will need a function or Case statement to control what gets turned on or off according to iGrp.

Select Case iGrp
Case 1
Me.cmdOpenMgr.visible = False
Case 2
Me.cmdOpenMgr.visible = True
End Select

I have apps which get the Windows UserID and then looks up the group and goes from there. No login form required.

Hope this helps!

Thank you for that mate. I will have a go and get back to you.

Cheers

Tej

[Tej21]

Hi jaxjagfan

Im still having some trouble. I got up to the point at which im able to login with different accounts. Here is the code:

Option Compare Database
Private intLogonAttempts As Integer

Private Sub Form_Open(Cancel As Integer)
Me.cboUser.SetFocus
End Sub

Private Sub cboUser_AfterUpdate()
Me.txtPassword.SetFocus
End Sub

Private Sub cmdLogin_Click()

    If IsNull(Me.cboUser) Or Me.cboUser = "" Then
            MsgBox "You must enter a User Name.", vbOKOnly, "Need a Username"
            Me.cboUser.SetFocus
        Exit Sub
    End If
    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
            MsgBox "You must enter a Password.", vbOKOnly, "Need a password"
            Me.txtPassword.SetFocus
        Exit Sub
    End If

    If Me.txtPassword.Value = DLookup("password", "tblUsers", "[userID]=" & Me.cboUser.Value) Then

        myUserID = Me.cboUser.Value
                
        DoCmd.Close acForm, "frmLogon", acSaveNo
        DoCmd.OpenForm "frmMainMenu"
        
        Else
        MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
        Me.txtPassword.SetFocus
    End If
End Sub

Ive created my main menu form which has button1, button2, button3 etc.

Ive also created similar code to what you said:

Private Sub Form_Open(Cancel As Integer)

    If accessLevelID = 1 Then
    Me.button1.Visible = True
    Me.button2.Visible = True
    
    Else
    Me.button1.Visible = False
    Me.button2.Visible = False
    
    End If

End Sub

......right, but i cant catch what username was chosen in the drop-down menu with its access level id to get the chosen buttons displayed.

The following attributes are in tblUser:

userID
surname
firstname
accessLevelID

>>> with accessLevelID being 1 for Admin, 2 general user etc

If its not a problem, i could use some help.

Thanks

Tej