Link to home
Start Free TrialLog in
Avatar of esbyrt
esbyrtFlag for Canada

asked on

How to filter records based on user's status

Hi.  I have a project database with tblProject, tblUser and tblCollaborators.  In tblCollaborators there is UserID, ProjectID and UserLevel - which will be either admin or Read Only.  I want to be able to pull up all the project records that a user is Admin for. Each project can have several Admins who need to edit the project and a user may have Admin access to several projects.
Can this be done from a command button that opens the project form? I thought of doing a query to filter for UserID and Admin level but I'm not sure how to implement it so that the current user is automatically filtered for.  ( I do have a login form that will stay open and hidden after the user logs in)
Ideas anyone?
Thanks!
Avatar of Sheils
Sheils
Flag of Australia image

You can insert a button in the form and use the following code for its onclick event:

Me.Filter = "UserLevel = 'Admin'"
Me.FilterOn = True
Hello,

It appears to me that you need a query in order to view the data. If you just want a list of admin users you could use a query like: SELECT * FROM [tblUser] WHERE [tblUser].[UserLevel]='Admin'; That will simply tell you which users have the status of "Admin".

I hope that helps.
-Chris
Avatar of esbyrt

ASKER

Hi.  Thanks for the suggestions but I need to find Projects for the current logged in user only, and only projects that user is an admin for.  Maybe something like SELECT * FROM [tblUser] WHERE [tblUser].[UserLevel]='Admin' AND [userid]=forms![frmLogin]![UserID]  ?  Is that syntax even close to right?  Then I still need to fetch the projects from the projects table.
ASKER CERTIFIED SOLUTION
Avatar of Sheils
Sheils
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of esbyrt

ASKER

Hi.  Sorry I've taken so long to come back to this.  Crazy busy.  So I've set the login form to pick up the global variable as you suggested.  I have my qryAdminbyProject returning only projects that the current user is an admin for by filtering for the global variable and "admin" userlevel. So far so good.  

What I'm having trouble with is filtering the project form to return only those projects that are present in the qryAdminbyProject.  I have a command button [cmdMyProjects] that opens the Projects form with a macro.  I have tried setting the Filter Name to the query but that isn't working ( I get a parameters popup for UserID and UserLevel).
Any other suggestions?
Thanks!
I suggest that you stop using macro. You have much more control over what's happening if you replace all macro with vba.

Use DoCmd.OpenForm method to open the form. The syntax for using this method with filters is available at this link: http://msdn.microsoft.com/en-us/library/office/ff820845%28v=office.15%29.aspx

 I get a parameters popup for UserID and UserLevel.

Remove the criteria from qryAdminbyProject  and let it return ALL result. The filtering will be handled by the docmd,openform method. This will also allow you to use this query for other forms where you don't need filter or need a different filter
Avatar of esbyrt

ASKER

Argg!  Ready to tear my hair out here.  Okay the Where condition doesn't do the job as far as I understand because I need to pass a variable number of arguments depending on how many projects the user is admin for.  So it follows that that the FilterName method of docmd.openform might work but doesn't the query itself need to be filtered to accomplish that?  I tried adding the following to my command button to open the Projects form.
Private Sub cmdMyProjects_Click()
DoCmd.OpenForm "frmProject", acNormal, "qryAdminbyProject"
End Sub
But I still get a parameters popup looking for tblCollaborators.UserID and another for tblCollaborators.UserLevel.
My qryAdminbyProject open fine by itself and returns the correct records.  It is based on tblCollaborators with both UserID and UserLevel filtered in the query and all fields from tblProjects.

Please forgive me if I'm missing something here.  If you are suggesting using Where to filter -how do I do that for multiple records? And if you mean the FilterName method, why does it ask for parameters when the query itself runs fine?
Post a sample database and I will try to figure out what the problem is. Live a few dummy data ~10 records in the relevant tables
Avatar of esbyrt

ASKER

Hi.  Okay I got that one figured out.  I changed my form to use the query as its record source so that returns only the Project records the current user is either an admin for or the creator of.
Now there's another issue of course.  
Here's my query sql.
SELECT tblCollaborators.UserID, tblCollaborators.UserLevel, tblProject.ProjID, tblProject.ProjName, tblProject.*, tblProject.CreatorID, *
FROM tblProject INNER JOIN tblCollaborators ON tblProject.ProjID = tblCollaborators.ProjectID
WHERE (((tblCollaborators.UserID)=forms!frmLogin!txtUserID) And ((tblCollaborators.UserLevel)="Admin")) Or (((tblProject.CreatorID)=forms!frmLogin!txtUserID));

This is the query results.
UserID      UserLevel      tblProject.CreatorID      tblProject.ProjID      tblProject.ProjName
3            Admin                              3                                 1                      First Project whatever and then some
2           Read Only                              3                                 1                      First Project whatever and then some
4           Admin                              3                                 1                     First Project whatever and then some
3           Admin                              1                                15                     Peppe Lepeure

I would like to see ProjID 1 only once but unique values or SelectDistinct doesn't work because each record is in fact unique the way it is.  How do I filter the project id to show only once?
(Should I be posting this as a new question?)
Thanks!
Avatar of esbyrt

ASKER

Thanks for your help.  I have posted the remainder of the problem as a new question.