esbyrt
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!
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!
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]='Adm in'; That will simply tell you which users have the status of "Admin".
I hope that helps.
-Chris
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]='Adm
I hope that helps.
-Chris
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]='Adm in' 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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
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
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?
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
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!fr mLogin!txt UserID) And ((tblCollaborators.UserLev el)="Admin ")) Or (((tblProject.CreatorID)=f orms!frmLo gin!txtUse rID));
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!
Now there's another issue of course.
Here's my query sql.
SELECT tblCollaborators.UserID, tblCollaborators.UserLevel
FROM tblProject INNER JOIN tblCollaborators ON tblProject.ProjID = tblCollaborators.ProjectID
WHERE (((tblCollaborators.UserID
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!
ASKER
Thanks for your help. I have posted the remainder of the problem as a new question.
Me.Filter = "UserLevel = 'Admin'"
Me.FilterOn = True