Hello, I originally posted this under another title which was wrong and thought this was a better description if someone else was having the same problem. I have a navigation form (tabs Home and My Employees "subforms" for frmHome and frmAdminEmployee respectively) I have set up two different levels of access. Security level (limits the type of editing/viewing) and AccessLevels (which limits the user to their employees). A employee will sign in via a Login form and the Security and AccessLevels transfer to the navigation form. With that being said, I am attempting to limit the ability of supervisors to view only their employees (via the AccessLevel). I have the ability to pull the list from a qry which is dependent on their AccessLevel. There is a many to many relationship between tblSupervisor and tblDepartment. I have created a Junction table tblSupervisor_Department. When I use the junction table, it is tripling the number of employees unless I groupby. Not using GroupBy poses the problem with 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 (understandably). 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 that works for pulling that information.




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 based on the groupby qry) with after update go to record with the same ID and it wont open the form in the navigation form, but as new form. If I do nothing with the filters, Problem there is that the form opens to all employees. I do not want the form edited by specific AccessLevels/Security so I cannot set to new and then just click on the list. Attempted to have the list on the tab "Home" which contains the "subform" frmHome. Attempted to build vba to click on that list and open the tab "MyEmployees" frmAdminEmployee, without success. Any help would be appreciated. At a loss.