Limit records based on login

[shards]

I am creating an HR (human resources) database that will be used by both the HR department and all supervisors. HR wants to limit what supervisors can see—they want them to see, add performance evaluations, do consultations, etc. only for those employees who report to them. BUT…all of HR should be able to see all records and some managers shouldn’t be restricted either (managers essentially just have "children" of supervisors).

On the main employee table td_employee there is a [supervisor] field on each employee record—each employee record is unique based on the [employee_number] field. There are many select queries throughout the database that allow lookup fields on the forms based on the [employee_number] field.

I have created a login form f_login and table td_security for setting up usernames and passwords for the database. The f_login form has two controls: cboUsername and txtPassword to capture the login info—the command button on the form executes the login script on click. The login I have works wonderfully.

On the td_security table I added a field called [access_level] that differentiates users from one another--values are H, M and S: H & M should essentially see everything but H will have a different main navigation menu. S level will only see records for those employees who they are attached as the [supervisor] for.

However, I have searched and searched and cannot find anything that will help me (I am a novice programmer at best) to limit the records that a supervisor can see across the entire database based on their login. Help?

FYI - we are using Access 2007.

 

[CaptainD]

I have two programs that I limit access via a security level. When the person logs in I set a Public variable in a module to their security level. Most of the time I use the security level to enable/disable menu and tab items but there are a few instances where the SQL statement is specific to the access level.

 

[ck1999]

Why not after the initial log in screen based off of a dlookup determine the [access_level] this if access_level = H then switchboardH is displayed, if M then switchboardM is displayed, etc.

Then when opening forms and reports the commandbuttons on the switchboards can be used to filter data

I hope I am explaining this well and it is what you are trying to accomplish.

ck1999

 

[shards]

Thanks to you both. However, I'm looking for information on filtering down to the supervisor only being able to view records of those who report to him. I don't believe that setting something up only on the access level will accomplish what I need to do. ?

 

[ck1999]

If you have a field for each employee called supervisor.

Then on the second form I explained earlier lets say John (loginID) signs is as a supervisor.

Then the second form (Switchboard) you can filter when he tries to enter another form or report a where clause that says

where supervisor=loginID

This would allow the viewer (in this case) john only to see records for employees where he is the supervisor.

Then for a level up you have a similar situation. Make a table with fields called supervisor and manager

then make an innerjoin between this table and the employee table with a where clause stating manager=loginID

I hope this is a little clear then mud. I understand it.

ck1999