Switch Function

[Harvard]

I'm trying to create a query that returns only the records of the current
user. I've decided to use a switch function. And it appears to be working.
My only problem is that I don't know how to get the switch to return multiple
values. If I log on as admin, i want to be able to see all user information.
Here's what I have. Can someone give me a hand?

Switch(CurrentUser()="admin","admin" And
"hjames",CurrentUser()="hjames","hjames")

 

[Michel Walsh]

Instead of

WHERE userID = SWITCH( ... )

try


WHERE SWITCH( CurrentUser()="admin", true,
CurrentUser()="support", userID <> "admin",
CurrentUser()="hjames", userID IN("hjames",
"accounting"),
CurrentUser()="accounting", userID =
"accounting",
..., ... )



But since this implies you have to change the SQL statement to change the
access rights, I would rather use a table:

AccessRigths
UserID CanAccess 'fields
"admin" "admin"
"admin" "support"
...



and use an inner join:


FROM myTable INNER JOIN AccessRights
ON myTable.userID = AccessRIghts.CanAccess

WHERE AccesRIghts.UserID= CurrentUser()



and then, to modify the access rights, it is only a matter of modifying data
in a table (NOT to modify data in code).




Vanderghast, Access MVP

 

[Harvard]

I'm really interested in doing that table thing. However, it seems a little
more complicated than i'm used to. Think you could write some instructions
for step by step implementation??

 

[Michel Walsh]

You need, basically, a table with 2 fields, one that will store the possible
CurrentUser() values, and the second field storing the 'id' that the user
can see. As example, if admin can see (admin, john, mary), john can see
(john, mary) and mary can only see mary, the data in the table will be:

AccessRIghts 'table
UserID CanSee ' fields
admin admin
admin john
admin mary
john john
john mary
mary mary



Now, if the table to open has a field, let us call it "whoWriteIt":


MyTable 'table
primaryKey, ..., whoWriteIt ' fields
1, admin
2, john
3, john
4, mary
5, mary



to be able to view only the records you are allow to see, in MyTable, you
can use the following query:


Bring the table MyTable in a new query. Also bring table AccessRights.

In the upper half portion of the query designer, drag the field whoWriteIt
over the field CanSee. That makes your inner join.
Drag the field UserId from AccessRights into the grid, add the criteria,
under it, = CurrentUser()
Drag the fields you want from MyTable in the grid.

That's all.


Edit table AccessRights to add/remove rights to see the records in MyTable.


It would be highly preferable to make the two fields of AccessRights the
primary key. It would be preferable to add the following indexes: one on
UserID, one on CanSee, one on whoWriteIt.



Hoping it may help,
Vanderghast, Access MVP

 

[Harvard]

Okay, I made the inner join. I dragged the UserID on the grid. Now where do
i add the criteria? I tried adding the criteria to the userID but that
didn't work out.

 

[Michel Walsh]

If you try with a constant, say

= "john"

rather than by

=CurrentUser()


does it work?

If it works for a constant, then, when you type

=CurrentUser()


does Access add quotes? if so, remove them. If that still does not work,
does


? CurrentUser()

works, when typed in the Debug Immediate Window (Ctrl_G) ?




Vanderghast, Access MVP