User login form with different levels of access?

[Clayhead22]

Hi. I have recently started a project and become stuck already. I have a login form created that works (code below). What i want to do is allow access to the users of 1-7 in table (level of access) which will determine which page they view after logging in. IE if the have Access in table "Users" as "1" then when they log in the will see a form thats called L1. Same with 2,3,4,5,6 & 7.
Hope you can help.

Private Sub LoginButton_Click()
If IsNull(Me.LoginUsernameText) Then
    MsgBox "Please Enter Username", vbInformation, "Username Required"
    Me.LoginUsernameText.SetFocus
ElseIf IsNull(Me.LoginPasswordText) Then
    MsgBox "Please Enter Password", vbInformation, "Password Required"
    Me.LoginPasswordText.SetFocus
Else
    If (IsNull(DLookup("[Username]", "Users", "[Username] ='" & Me.LoginUsernameText.Value & "' And password = '" & Me.LoginPasswordText.Value & "'"))) Then
    MsgBox "Incorrect Username or Password"
    Else
    MsgBox "Password accepted! Welcome!"
    DoCmd.OpenForm "Interface"
    End If
    End If
End Sub

 

[Isskint]

hi clayhead

This is a fairly simple thing to achieve. You have already made use of an extra table to hold details of your users and their passwords, so just add an extra field to hold their start up form name. Once a users details are confirmed, Dlookup() the name of the opening form.

However, if you wish to apply security to multiple forms, then you will need to do the following;

• Store the ID of the current user (usually done with either a global variable or an unbound field on each page that you pass the users ID to each time a form is opened)
• Store the minmum security level required to open each form (either in the form or in a seperate table)
• Prior to opening a form check the current users security level with that of the requested form and act accordingly

 

[gemma-the-husky]

the login is relatively easy

however you then have to modify pretty well every form/report to comply with the entitlements you wish to grant, which is often not easy.

eg, It's not quite as easy as SIMPLY saying "allowedits = false", since that will disable unbound combo boxes, and so on - which is often not what you want.

 

[Clayhead22]

Hi Again thanks for the info. I have put something together which i think would be suitable for my needs but now when i click login the form does nothing. It plays the message password accepted but does not move on to any form. Please can anyone help?

Private Sub LoginButton_Click()
If IsNull(Me.LoginUsernameText) Then
    MsgBox "Please Enter Username", vbInformation, "Username Required"
    Me.LoginUsernameText.SetFocus
ElseIf IsNull(Me.LoginPasswordText) Then
    MsgBox "Please Enter Password", vbInformation, "Password Required"
    Me.LoginPasswordText.SetFocus
Else
    If (IsNull(DLookup("[Username]", "Users", "[Username] ='" & Me.LoginUsernameText.Value & "' And password = '" & Me.LoginPasswordText.Value & "'"))) Then
    MsgBox "Incorrect Username or Password"
    Else
    MsgBox "Password accepted! Welcome"
    
    Useraccess = DLookup("Authorisation", "Users", "Username= 'LoginUsernameText'")
    If Useraccess = "1" Then
    DoCmd.OpenForm "InterfaceL1"
    ElseIf Useraccess = "2" Then
    DoCmd.OpenForm "Interface"
    End If
    End If
    End If
End Sub

How i have it laid out is how i want to open the forms by user access within the user table. Once resolved i will need to add 2 features 1) Check if the users status is blocked (Play a message and do not load) and 2) Allow a maximum of 3 login attempts.

 

[Isskint]

Are you certain Useraccess contains the text "1" or "2"? Does it contain the number 1 or 2?

 

[Mile-O]

Rather than all this password stuff, just authenticate based on their Windows logon (username).

So, two tables:

tblUsers
UserID [Autonumber, PK)
WindowsID
Forename
Surname
UserLevelID [Number, FK]
etc.

tblUserLevels
UserLevelID [Autonumber, PK)
UserLevel

Then a query that joins these two tables together on UserLevelID. The criteria of WindowsID to be fOSUsername() (use Google to find that function).

This will give you a query that will return no records if the user is not in the table and only one record, assuming each user only has one record in the tblUsers.

Then you can refer to the UserLevel field in the query to get the user id, so that rather than asking for a password, your database can just do something like this intuitively:

Select Case DLookup("UserLevel", "qryUserInfo")

    Case Is "Admin": DoCmd.OpenForm "frmAdmin"
    Case Is "Manager": DoCmd.OpenForm "frmManager"
    Case Is "Auditor": DoCmd.OpenForm "frmAudit"
    Case Is "Grunt": DoCmd.OpenForm "frmEveryoneElse"
    Case Else: DoCmd.OpenForm "frmAccessDenied"
End Select

Personally, I load the user info into a class when the database opens, and then I can just refer to the user's properties a la ThisUser.UserLevel