Avatar of esbyrt
Flag 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?
Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon

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

Me.Filter = "UserLevel = 'Admin'"
Me.FilterOn = True
Christopher Gay


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.

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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?

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

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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

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 for your help.  I have posted the remainder of the problem as a new question.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.