Manager Logon To Only See Departmental Staff Records Not The Whole Database?

[dapoole]

Hi there,



I'm developing a small Employee Performance Appraisal database and require that when each department manager logins into the database they can ONLY see the records of their own department employees. For example the Marketing manager will login and only see the records relating to his 5 staff from that department, whereas when the IT manager logs in he will see his 4 staff records. Does anyone know how to implement this or something similar?

TIA

[robrich22]

Well there are several ways, but I will name a few that come off the top of my head. First you will need to determine who is logged in. 1. Create a login form, with username and password (There are many threads on how to create a login form) 2. You could grab the current windows user.

Now your employee table should have a "Department" Field. You would setup the form's query to select only the records that the department field equals the manager's department. Such as SELECT * FROM Employees WHERE Department= CurrentUser.Department

[dapoole]

Ok I've managed to create a basic login form that matches the users name to the password field in the tblEmployee table so this now on correct log on it opens the frmEmployee form but displays all employees. So how and where should I code the SELECT * FROM tblEmployee WHERE deptID = CurrentUser.deptID ? In the frmLogon form or elsewhere? TIA

[robrich22]

Ok I've managed to create a basic login form that matches the users name to the password field in the tblEmployee table so this now on correct log on it opens the frmEmployee form but displays all employees. So how and where should I code the SELECT * FROM tblEmployee WHERE deptID = CurrentUser.deptID ? In the frmLogon form or elsewhere? TIA

Take a look at the following code. You will want to pass the department name as a filter, when you open the frmEmployee

Private Sub Login()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim sqlQry As String
    sqlQry = "SELECT [tblUsers].Username, [tblUsers].Password, [tblUsers].Department FROM [tblUsers] " _
           & "WHERE [tblUsers].Username = '" & txtUsername & "' AND [tblUsers].Password = '" & txtPassword & "'"
            
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(sqlQry)
    
    If rst.EOF Then
        MsgBox "Username or Password is incorrect."
        ' No records found in the database.
    Else
        ' Record was found
        DoCmd.OpenForm "frmEmployee", acNormal, , "Department='" & rst.Fields("Department").Value & "'"
    End If
    
    rst.Close
    Set dbs = Nothing
    Set rst = Nothing
End Sub

[dapoole]

Thanks for your help so far, I appreciate it. On reflection I should have posted my own code and table definitions to help work out the solution so I've added this below to see where I may be going wrong.

tblEmployee
empID AUTONUMBER
manID NUMBER
deptID NUMBER
foreName TEXT
surname TEXT
jobTitle TEXT
startDate DATE/TIME
managerRole TES/NO
password TEXT

Currently the below code opens the frmEmployee directly into the record of the user who logins in, however it only displays their record, not all employees from that department, which is what I'm trying to achieve.

Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box
    If IsNull(Me.cboUsername) Or Me.cboUsername = "" Then
            MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
            Me.cboUsername.SetFocus
        Exit Sub
    End If
'Check to see if data is entered into the password box
    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
            MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
            Me.txtPassword.SetFocus
        Exit Sub
    End If
'Check value of password in tblEmployees to see if this matches value chosen in combo box
    If Me.txtPassword.Value = DLookup("password", "tblEmployee", "[empID]=" & Me.cboUsername.Value) Then
        empID = Me.cboUsername.Value

'Close logon form and open splash screen
        
       DoCmd.Close acForm, "frmLogon", acSaveNo
       
      DoCmd.OpenForm "tblEmployee", , , "empID=" & empID
        Else
        MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
        Me.txtPassword.SetFocus
    End If
    
'If User Enters incorrect password 3 times database will shutdown
    
    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
        MsgBox "You do not have access to this database.  Please contact your system administrator.", vbCritical, "Restricted Access!"
        Application.Quit
    End If
    
End Sub

Thanks.

[robrich22]

Sorry it was taken awhile to get back with you. From a quick glance, I see that you are trying to open "tblEmployees", instead of "frmEmployees".

DoCmd.OpenForm "tblEmployee", , , "empID=" & empID

should be
DoCmd.OpenForm "frmEmployee", , , "empID=" & empID

Oh but you also need to pass the employee's Department to filter by Department not empID. I'll try to rewrite this later.