tables' fields and query statements

[Guest]

This is my scenario: Several different communities spread in 3 States.
• The same Director for each community;
• 1 Manager per State (however the managers can be responsible for more than
one State);
• 1 or more agents per community (however the agents can be responsible for
more than one community within the State).

I need to:
• Give “Director X†full access (able to filter by State, Community, Agent)
• Give Managers access to all communities within the State(s) where they
have “jurisdictionâ€
• Give Agents access to all communities where they have “jurisdictionâ€
within the State. Agents can see records belonging to another agent sharing
the same community.

Nevada:
Community 1
Director X
Manager A
Agent A
Agent B

Community 2
Director X
Manager A
Agent C

Community 3
Director X
Manager A
Agent A


California
Community 4
Director X
Manager B
Agent D
Agent E

Community 5
Director X
Manager B
Agent F
Agent E

Community 6
Director X
Manager B
Agent G
Agent D

Arizona
Community 7
Director X
Manager A
AgentH

 

[Allen Browne]

Rick, presumably you are using Access security (mdw) so people sign in. You
have probably already set up groups so you can determine each user's rights.

If so, you could use the Open event of each relevant form to set its
RecordSource, based on the group the user belongs to.

For OpenReport you can probably use a WhereCondition, but you would not want
to use filters for the form because it's too easy for the users to turn them
off.

 

[Guest]

Allen,

actually I'm not using Access security. I'm giving access to the users thru
ASP. That's where I'd like to control the access levels.

Thanks

Rick

 

[Allen Browne]

So you will need to formulate your SQL statement so that the WHERE clause
retrieves only the records for that user, however you determine that.