Using Navigation form, click on list box to open specific record

[mlrucci]

Hello, I am attempting to limit the ability of supervisors to view only their employees. I have the ability to pull the list in a qry dependent on their AccessLevel. There is a many to many relationship between tblSupervisor and tblDepartment. I have created a Junction table tblSupervisor_tblDepartment. when I use the junction table, it is tripling the number of employees unless I groupby. This poses the problem of the inability to enter data on the form if I set the record source to the qry. Currently I have it set to a tbl to avoid entry problem and each Supervisor sees all the employees. If the AccessLevelID is 1, then I need the Director to see all the employees, otherwise, it is specific to the other AccessLevel. Here is my qry


SELECT tblEmployee.EmployeeID, tblDepartment.DepartmentName, tblEmployee.EmployeeName, tblDepartment.DepatmentAccessLevelID
FROM (tblDepartment RIGHT JOIN tblEmployee ON tblDepartment.DepartmentID = tblEmployee.DepartmentID) LEFT JOIN tblSupervisor_Department ON tblDepartment.DepartmentID = tblSupervisor_Department.DepartmentID


GROUP BY tblEmployee.EmployeeID, tblDepartment.DepartmentName, tblEmployee.EmployeeName, tblDepartment.DepatmentAccessLevelID
HAVING (((tblDepartment.DepatmentAccessLevelID)=[Forms]![frmNavMain]![txtUserAccessLevel])) OR ((([Forms]![frmNavMain]![txtUserAccessLevel])=1))
ORDER BY tblDepartment.DepartmentName, tblEmployee.EmployeeName;


Any help or thinking outside the box would be much appreciated. I have tried multiple ways to make this work. Even tried to have a list box (list for double click based on the groupby qry) with after update go to record with the same ID. Problem there is that the form still opens to all employees. I do not want the form edited by specific AccessLevels so I cannot set to new and then just click on the list.

[mlrucci]

I forgot to mention that my frmAdminEmployee is a "subfrm" in my navigation form "frmNavMain".

[June7]

In what table do you want to edit/create records - tblEmployee?

Supervisor and employee are linked through department?

Perhaps need a form/subform/subsubform arrangement.

[mlrucci]

For this particular form frmAdminEmployee, I would like to edit through the Employee table. The purpose is to have one main form "frmNavMain" contain all the information they are going to need set in the tabs. This could include other forms based on other tables. I want only access to their employees to be accessible based on their access level. Each supervisor should not see the other's employees. Currently each employee has one department. Each department can have multiple supervisors and each supervisor can have multiple departments. (as to why I set up the many to many junction table). I have thought about building a subform that had all the employees the supervisor can edit, but it will eventually have to base that for multiple subforms and I have been unable to figure out how to filter all subforms with the same list. So very frustrated and lost. I am fairly new to vba and am self taught. Any help would be appreciated. I am including a stripped down version if you wouldn't mind taking a peak.

[June7]

Are you going to distribute db as a multi-user split design? Are you going to distribute with settings to prevent direct viewing of objects in Navigation Pane?

Really shouldn't have a separate table for supervisors. Supervisors are employees and supervisors have supervisors. Pull supervisors from tblEmployee and save that ID into tblSupervisors_tblDepartments, however, not seeing this junction table. The EmployeeID can also be the value saved into Supervisor field of tblEmployee. This is a recursive relationship, table is joined to itself in queries to show the employee/supervisor relationship.

Not really understanding how a person can supervise multiple departments and shouldn't a department have only one supervisor? I mean Accounting Dept has only one Manager, right? There might be subdivisions within a department that each have a supervisor but each organizational entity has only one head on the org chart.

I've never used Navigation Form in any db I built, don't like it. Will have to look at some more tomorrow.

[mlrucci]

June7, your information helped! because I had so many tables and joins, I made it confusing. I changed over and I put the IDs into the Employee table and now it seems to work with new qry. Thank you very much for your help!