We have an Access 2003 database that handles Contacts and Events for
the organization. This is used by everyone in the organization -
across all departments.

The senior directors now want to redesign it so that users can only
see the details of the contacts that their respective departments
created. The senior directors however want to be able to see all the
records for all departments. Basically, they don’t want the lower
echelons to see who their top level contacts are or their details or
to see which functions the directors have which are exclusive to the
directors.

Individual departments would still be able to administer their own
events entirely. For an organization–wide event, individual
departments would add names of invitees to these functions but only
the directors would see the entire invitation list.

To achieve this, is it just a question of creating a user table that
lists users logon names against their department name, using the
fosusername function, and then filtering each query result with the
department name?

I am concerned that this method may mess up the numerous queries,
forms and reports that underly the database. Or is there a neater way
to achieve this?

Vet Tech

On 10 Jul., 18:05, Vet Tech wrote:
One side of the task is to have a function to decide whether a given
user is allowed to see a given record. If every record contains the
department number, you can test this number against the department
number of the user, just as you say. The function will give true when
the department number is the same, OR when the user has, say,
department number = 999, that is: he is a director.

This function can be written to accomodate several levels of
permissions, e.g. when some user can see all records of several, but
not all, departments.

The other side of the task is, as you say, that you would have to add
this test to every query in the program, which can become very messy
indeed.

A way around this could be: if hopefully only one table is concerned,
you don't need to filter the queries, it is enough if you filter this
one table before the queries see it.

To do so you have to change the name of the table, let's say
tbl_contacts becomes tbl_contacts_first. Then you write a query to
filter tbl_contacts_first against the department number and you name
this query tbl_contacts.

This way all existing queries, forms and reports will see only the
allowed records, without having to be modified themselves.

Greetings
Marco P

Thanks Marco, I will now look into the implications of adopting each
of these however the pre-filtering suggestion does sound really neat.

Regards
VT