User level security to determine what records print in reports 1

[ajaeger]

First attempt at using Access security and I wanted to get some advice on how to structure my reports/forms.

I have groups defined as "Conference", "Membership", "Admin" etc. The users are assigned to one group. I have one data entry form that is currently supported by a query, qryData.

However, the data in qryData is for all the different departments (department numbers) in the organization. When someone in the Conference group logs into Access, I only want them to see the data that pertains to their department.

I was thinking that I'd create queries for each department, qryData-Conference, qryData-Membership... These would pull only the appropriate department numbers. Then in VBA, I'd alter the form's record source to match the appropriate query, based on the user ID.

I was thinking of doing something similar for the reports.

Is this the correct way of going about this?

Another option I considered was creating duplicate forms/queries for each department and assigning permission that way, but if I have a change to the form, it would mean changing it several places. Also for my reports, as there are many, it just seemed to much.

Thanks.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[BSman]

I gave up on using Access security a long time ago, and am happy that I did so because when you need to upgrade to a new version of Access and have workgroup security it can be a real headache.

Here's a way to handle your situation. Have your own login form using a user list you set up in a table. The user list should include one or more columns related to security, such as access level and department.

When a person logs in with your login form, the values for access level and department should be saved in public variables (that you have set up in a module), so they are available throughout your application.

Also set up functions in the module that return the value of each of the public variables. You must do that so you can refer to them when you build your queries that you will be saving.

Depending upon the complexity of your requirements you can use these types of methods:

For the simpler requirements where one value will match a column in your data source, just have your queries set up with the criteria that the (for example) value of the department column in the data source must equal the value of the logged in user's department.

If there can be multiple values for a column for a specific (for example) access level value, you can handle that with another table. Let's say that the access level will define what department(s) a user has access to. This table would have two columns, the access level and the department. There could be multiple rows for the same access level, like this:

Access level Department
Payroll Payroll
Employment Employment
IT IT
IT Payroll
IT Employment

The query would then link the main table with this table by the Department column, while the criteria would be where Access level equals the value of the user's access level. In the example above, users in each of the first three departments (access levels) would only see their respective departments, while someone with IT access level would see IT plus the other three departments.

Bob