We help IT Professionals succeed at work.

How to filter records based on user's status

esbyrt
esbyrt asked
on
257 Views
Last Modified: 2014-05-30
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!
Comment
Watch Question

Commented:
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 GayIT Operations Manager

Commented:
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

Author

Commented:
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.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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!

Commented:
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

Author

Commented:
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?

Commented:
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

Author

Commented:
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!

Author

Commented:
Thanks for your help.  I have posted the remainder of the problem as a new question.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.