record security

[robert909]

Hi,

I'm using Access 2000 on Windows 2000 and got the following question:

We've set up a database where you have data per factory. So suppose in the table "T_factory" are the fields "factory" (text-field), "machinenumber", "purchasedate". Now I've tried to build a module which takes care of record-security. The persons that log in are only allowed to see the data from the factory that they work in. But there are some persons that are allowed to see the data from all factories.

The module looks like this:

Public Function secu_level()
If CurrentUser() = "Jim" Then secu_level = "factory1"
If CurrentUser() = "Roger" Then secu_level = "factory2"
If CurrentUser() = "Jane" Then secu_level = "factory3"
If CurrentUser() = "Hank" Then secu_level = "like"*""
End Function

I'm using the variable "secu_level" as criteria for the field "factory" in the query that is the basis of a form. (so "factory1" is an example of a value of the field "Factory").

Now this works fine for all users that need to have access to only 1 factory. But I can't find how I can give a user access to all factories. I've tried it for user "Hank" with the value "like"*"" for the variable secu_level, but that doesn't work. I don't get any data at all for this user. And it should also be possible to give a user access to 2 factories.

So I've got no problems with giving a user access to 1 factory, but as soon as a user needs access to 2 or more factories, I get in trouble.

Do you know how to solve this?

Thanks in advance for your help!

Robert

 

[Mile-O]

A junction table, perchance?


A new table, tblAccess with the User table's primary key and the Factory table's primary key combining to become a composite key.

Now relate this table to the relevant fields in the user and factory tables.

When you get the username you can check, with a lookup if the user has access to whichever factory.

 

[robert909]

Hi,

This works, thanks a lot for your advice!

Robert