User restrictions. Please Help

[Dherrera]

ive got a form that has a combo box with all employees on it. selecting a name fills in all fields such as address, city, etc, as well all that employees reports in a subform.
each employee has a user level, 1 - 4, with 1 being the top and 4 the bottom.

what i want to do is when ever a user logs on i want them to be able to view only employees with a user level lower than theirs. for example, Joe's user level is a 3 and he logs on, he should only see employees with a user level of 4 in the combo box.

any idea on how to accomplish this?

 

[psemianonymous]

SELECT A.[Username], A.[UserLevel]
  FROM [USER_TABLE] AS A, [USER_TABLE] AS B 
  WHERE A.[UserLevel] >= B.[UserLevel] AND
        B.[UserName] = 'functionCallThatGrabstheUserName()'

While we're here, you might want to check out my security FAQ just in case you aren't aware of all Access' security problems:

Gauging your security needs; alternatives to Access/JET security faq181-3893

 

[Dherrera]

how do you put the function call in the SQL statement? its not working for me.

here is my sql statement:

"SELECT userName, userLevel FROM tblEmployees WHERE
 ManagerID = GetUserName()"

 

[psemianonymous]

If you're generating this in VBA code, you do something like

"SELECT userName, userLevel FROM tblEmployees WHERE
ManagerID = '" & GetUserName() & "'"



--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm