Current User Determines Recordset

  • Thread starter Thread starter Pete T
  • Start date Start date
P

Pete T

Sorry, This is a Re-post as the post I did yesterday that seemed to
have disappeared. In our Office we have a 5-yr old Database with staff
information on training, leave records, operations, etc. There are 7
teams and all staff are assigned to one of these 7 teams. We have 7
supervisors. Management now wants each supervisor to ONLY access
their Teams data. But, selected staff(me, Directors, Ops Coord.),
must have access to all data.

It seems to me that There must be a VBA Code that will grab the
Current User information and from this information create a recordset
of data that keys on that superviros Team number (1-7). This field is
commond amoung all tables.

Is there a way to set-up code that at login to the Database will
create the recordset available to all quieries? A Master Queries by
which all other queries are based? And how would the code be set-up?

Thanks Pete
 
Pete said:
Is there a way to set-up code that at login to the Database will
create the recordset available to all quieries? A Master Queries by
which all other queries are based? And how would the code be set-up?
Click to expand...

Well it's possible in a way but it will be a lot of work and would impose
severe constraints without an enormous amount of programming.
You would need a central database where ALL data maintenance could be
done. The super users could use this database as their application data.

Each user could be identified on login if you implement Access security.

Each user would have a copy of the database initially with empty tables.
When they started this database the applicable data could be copied from
the central database into the local tables. Obviously this could make the
startup fairly time consuming. The startup routine would also have to
delete all data in the local database before fetching the up-to-date data.

Quite clearly, this means that these users could not update the database.

If this is not acceptable then you are faced with a bigger task.
You will have to examine every form report and query and modify the
selection criteria in each case to include a rule based on current user.
And that will be even more complex because of the requirement to allow
some users to see everything. This may well mean you are going to have to
develop either alternative queries or develop some very complicated
selection rules. You might well think that it is worthwhile to have
different front-ends for the two different classes of user.

Regards

Peter Russell
 
Here's one fudgy, but very easy, solution.

Say you have a table T containing data that should be restricted by user.

Rename that table to T_Old (or whatever). Then create a new query as
follows:

SELECT * FROM T_Old WHERE TeamNumber = 3

If you named that query T, then, this is what you have achieved. Previously,
T was a table containing all of the data. Now, T is an updatable query
containing just the data for team 3. Every other query, form & report etc.
that previously showed *all* data, will now only show data for team number
3. And you have achieved this with no recoding of those queries, forms,
reports & modules!

Of course, you would need to determine the appropriate team number at
runtime - not hard code it to 3. There are various ways of doing that. For
example, if each user was required to log on with a username, you could get
that name from the CurrentUser() function, & index into a table that stored
each user's team number.

HTH,
TC
 
Back
Top