MS Access 2010 login form with access levels

Question

My management wants an access database that can have a way to control which users can see specific fields on a form. For example, if a manager logs in, a form will display the performance rating field. For any other user, the performance rating field will not be visible.

So far the below are some of my options:

1) Use VBA to detect the User Name of the access application and if its Manager's name, then the textbox is visible.

2) Use a username reference table that requires users to login. Users with special access will have the textbox visible.

3) Have a special little button on the form that, if someone clicks, will load a small password dialog and then set the text visible.

Which option would be the most difficult to implement?

Answer 1

I've implemented option nr 1 and 2, they we're quite easy to build. option nr 3 seems equally difficult.

The question within my work environment would be which option would be low in (account)maintenance and which would demand little effort from the users.

From that view option nr.1 has been more successfull.

(And i would rather build different forms in stead of turning the view setting of field ON/OFF)

Answer 2

The idea of using a Login form for user specific information is something that is available all over the internet. I would suggest a combination of all three of your ideas would be just the perfection solution.

For either of the two methods you have (1 & 2) you first need a Table that will hold the information. The table does not need to be complex (at least not right away). The table should be of the following structure,

tbl_Employees
-------------
EmpID       -   Auto Number -   PK
EmpNam      -   Text
EmpUserName -   Text
EmpPassword -   Text        -   Input Mask (If required)
IsManager   -   Yes/No      -   Default - No 

Then you will have to create a Form, a basic form that will have three controls, two Text Boxes and one Button. The first text box is where you will enter the Employee User name and the second for Password. Finally a button, behind which the magic happens. The code (simplified) behind the button would be something along the lines of.

Private Sub LogInBtn_Click()
On Error GoTo errOccured
    Dim eQryStr As String, empRS As DAO.Recordset

    eQryStr = "SELECT EmpID, EmpUserName, EmpPassword, IsManager FROM tbl_Employees WHERE EmpUserName = '" & Me.UserNameTxt & "'"

    Set empRS = CurrentDb.OpenRecordset(eQryStr)

    If empRS.RecordCount <> 0 Then
        If Me.PwdTxt = empRS!EmpPassword Then
            If empRS!IsManager Then
                DoCmd.OpenForm "ManagerForm"
            Else
                DoCmd.OpenForm "EmployeeForm"
            End If
            Me.Visible = False
        Else
            wrongEntry "Password"
        End If
    Else
        wrongEntry "User Name"
    End If
exitOnError:
    Set empRS = Nothing
    Exit Sub
errOccured:
    wrongEntry "User Name/Password"
    Resume exitOnError
End Sub

Private Sub wrongEntry(entityStr As String)
    MsgBox entityStr & " is incorrect (OR) omitted, please check again.", vbCritical
End Sub

As you can see, I have used

(a) A Recordset object than a simple DLookup. I prefer recordset object, you can use a DLookup, but you have to make sure you handle Null (if the criteria is not met).

(b) A separate Form for Managers and Employees. I imagined there would be a lot more on a managers form that is not available on a Employee form. If you do not wish to go this way, but use one form you can, but you need to inform the opening form of who is logging in - Using the OpenArgs property of the OpenForm method.

Just if you feel to simply avoid all the hassle and use an Password box, to get the access of the TextBox. Simply follow the instructions on this thread - Password Box - A variant of Input Box. Then create a button on the form you currently have, then on click of the button, you simply code the following.

Private Sub AllowAccessButton_Click()
    If Call_Password_Box = "yourPassword" Then
        Me.yourHiddenTextBox.Visible = True
    Else
        MsgBox "Sorry you are not authorised to vies this information (OR) Incorrect Password", vbCritical
    End If
End Sub

PS: The Hidden text control should be set to invisible, preferable in the Form current event.

I hope this helps.