Showing Only Your Data

[Emma]

Hi All,

I am looking for some advice. I have been using access
databases for quite a while but mainly for use by only
one or two users.

I now have to set up a system to be used by 50 or so
people, i have set up security levels and
usernames/passwords for each, this isn't a problem.

However i want the following anyone with 'level one'
access can only view their own records, is there someway
of appending the username name to each record to ensure
they only see information on forms and reports relating
to their username.

Additionally, i have managers with 'level two' access and
i want them to be able to see records on forms and
reports based on their own staff (level one). I want to
be able to specify, in a table if possible, which level
one usernames connect with which level two usernames.

And as a final level, i have level three access which
should be able to see all records for all people (one and
two)

I appreciate any advice, pointers, steps in the right
direction anybody can give me.

Thanks
Emma

 

[Jack MacDonald]

You will need to look at several things:

Read With Owners Permission (RWOP) queries allow you to restrict the
records that particular people can see. They are explained in the
Microsoft Security FAQ - see the site in my signature for a link.

You will need to construct queries that return the appropriate records
for the current user. The CurrentUser() function returns the name of
the user who is currently logged in.

Normally you would add a "owned by" (or similar) field to your
database tables. You would populate that field when a record is
updated using the form's BeforeUpdate event.

You would use the "owned by" field and the CurrentUser() in your query
as part of the selection criteria. Since you want certain people to
see records from multiple other people, you will need to go beyond the
basics...

The solution will probably involve a new table with the name of the
user plus a group that the user belongs to. The bosses would have
rights to all the records for their group, but not to others. Again,
you would encode that selection criteria into the RWOP query. The
table of users might look something like this:

tblUsers
UserName (PK) (= CurrentUser())
BelongsToGroupID
OwnerOfGroupID

Everyone would have an entry in the BelongsToGroupID. Only the bosses
would have an entry in the OwnerOfGroupID field.

HTH

Hi All,

I am looking for some advice. I have been using access
databases for quite a while but mainly for use by only
one or two users.

I now have to set up a system to be used by 50 or so
people, i have set up security levels and
usernames/passwords for each, this isn't a problem.

However i want the following anyone with 'level one'
access can only view their own records, is there someway
of appending the username name to each record to ensure
they only see information on forms and reports relating
to their username.

Additionally, i have managers with 'level two' access and
i want them to be able to see records on forms and
reports based on their own staff (level one). I want to
be able to specify, in a table if possible, which level
one usernames connect with which level two usernames.

And as a final level, i have level three access which
should be able to see all records for all people (one and
two)

I appreciate any advice, pointers, steps in the right
direction anybody can give me.

Thanks
Emma

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security