Solved

restricting query to results based on record in another table

Posted on 2011-02-11
8
547 Views
Last Modified: 2012-05-11
I have a table with a field in it (ResponsibleFor) that is a multiselect field. It holds a list of departments the staff member is responsible for. This table also holds the department that the staff member works in.

I have a separate table with personal notes for all staff members.
I have another table listing the departments of course, which populates the multiselect list boxes for the ResponsibleFor field.

I want to limit the results of a query (and in turn the subform I have made) to show only those results that a member of staff is responsible for. IE, TeamLeader1 is logged in (I can get the username from a variable) so the results of the query should display only staff in the first table that TeamLeader1 is responsible for.

Right now, if I run my subform standalone, and manually enter a team that exists in the popup it creates, it works as expected - restricting the results to the team members who are in that departments personal notes.

However, when I embed that subform in the main form I want to use it in, I cannot get my head around how to make it show only results (ResponsibleFor) based on who's currently logged in - ie, "TeamLeader1" or whatever. This variable is global so I can grab this at any time.

I appreciate that I may not have been that clear, but I hope I've given you enough to go on.

Any assistance gratefully received.

Thanks

Ross
0
Comment
Question by:RossDagley1
  • 6
8 Comments
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
If the form works as a stand alone form but not as a subform then it is going to be due to the code that is in form not taking account of it now being a subform.

References which worked as
Forms!formname!controlname

now need to be.
Forms!Mainformname!subformcontrolname.Form!controlname

subformcontrolname is the name of the container control on the main form that holds the subform.  This could be same as the name of the subform, but it depends how you added the subform to the main form.
0
 
LVL 84
Comment Utility
Are you referring to a Multivalue field in Access 2007 or 2010? If not, can you describe more about your table structure?
0
 
LVL 2

Author Comment

by:RossDagley1
Comment Utility
Thanks so far guys.
I'm using Access 2007.

I have a table that stores (amongst other things) the login name of all users (NetworkLogin). This table also stores the teams that a user might be responsible for (most staff are "none" but some are specific teams). This "ResponsibleFor" field is a multiselect field.

I have a separate login system for this database that works fine. It stores the currently logged in user in a global variable. This login name will match the data in the field NetworkLogin.

I want a query to display the results based on the staff member logged in - they should see only the results for the teams they are responsible for.

Currently, when I run my query, it prompts me (of course) to enter a team. When I enter a valid team name, it gives the results I expect.

However, I want the query to report based on the user running the query - as I say, I have the variable already of who is logged in and it will match a record in the staff table - in turn, the query should see who that staff member is responsible for, and return only those results.

I hope that's clearer!

Cheers!
0
 
LVL 2

Author Comment

by:RossDagley1
Comment Utility
I have a query (qryResponsibilities) that uses the current logged on user to show a list of the teams that user is responsible for. This works OK.

I am currently trying (I don't know if this is the best way) to run a second query (which the subform will actually be based on) that uses the results of the first query to extract the teams that the current user is responsible for, and display only the records for other staff in those teams.

First query:
SELECT qryStaffExtended.[Contact Name], qryStaffExtended.NetworkLogin, qryStaffExtended.[Payroll Number], qryStaffExtended.ResponsibleForLookup
FROM qryStaffExtended
WHERE (((qryStaffExtended.NetworkLogin)=LAS_GetUserName()));

Open in new window


Works. It displays the teams the current user (LAS_GetUserName) is responsible for.

Second query:
SELECT tblOne2OneMinutes.ID, qryStaffExtended.Department, tblOne2OneMinutes.StaffPayrollNumber, tblOne2OneMinutes.Document, tblOne2OneMinutes.DocumentNotes, tblOne2OneMinutes.LastEditedBy, tblOne2OneMinutes.LastEditedDate
FROM tblOne2OneMinutes INNER JOIN qryStaffExtended ON tblOne2OneMinutes.StaffPayrollNumber = qryStaffExtended.[Payroll Number]
WHERE (((qryStaffExtended.Department) In ([qryResponsibilities]![ResponsibleForLookup])));

Open in new window

Doesn't work. Still prompts me for "ResponsibleForLookup"

Any pointers?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Author Comment

by:RossDagley1
Comment Utility
ok - I'm getting closer :p

I've made some changes to the above, so please ignore.

qryResponsibilities shows which team(s) the current logged in user is responsible for, and this works fine:
SELECT qryStaffExtended.[Contact Name], qryStaffExtended.NetworkLogin, qryStaffExtended.[Payroll Number], qryStaffExtended.ResponsibleForLookup
FROM qryStaffExtended
WHERE (((qryStaffExtended.NetworkLogin)=LAS_GetUserName()));

Open in new window


The second query (the one the subform will be based on) works fine like this:
SELECT tblOne2OneMinutes.ID, tblOne2OneMinutes.StaffPayrollNumber, qryStaffExtended.Department, tblOne2OneMinutes.Document, tblOne2OneMinutes.DocumentNotes, tblOne2OneMinutes.LastEditedBy, tblOne2OneMinutes.LastEditedDate
FROM tblOne2OneMinutes INNER JOIN qryStaffExtended ON tblOne2OneMinutes.StaffPayrollNumber = qryStaffExtended.[Payroll Number];

Open in new window

But obviously lists all the records.

I want the second query to limit the results to the team(s) the current logged in user is responsible for - We've got that in the first query, I just cant figure out how to "pipe" the output into the second query.

Thats probably all I need.

any help please guys?

0
 
LVL 2

Author Comment

by:RossDagley1
Comment Utility
If I use:

SELECT tblOne2OneMinutes.ID, tblOne2OneMinutes.StaffPayrollNumber, qryStaffExtended.Department, tblOne2OneMinutes.Document, tblOne2OneMinutes.DocumentNotes, tblOne2OneMinutes.LastEditedBy, tblOne2OneMinutes.LastEditedDate
FROM tblOne2OneMinutes INNER JOIN qryStaffExtended ON tblOne2OneMinutes.StaffPayrollNumber = qryStaffExtended.[Payroll Number]
WHERE (((qryStaffExtended.Department) In ([qryResponsibilities]![ResponsibleForLookup])));

Open in new window

In my second query, it prompts me for ResponsibleForLookup - if I enter a Valid team name in there, it does in fact restrict the list correctly.

So - it appears the problem is getting the output from the first query into the second. My brain now hurts.

thanks!
0
 
LVL 2

Accepted Solution

by:
RossDagley1 earned 0 total points
Comment Utility
Found my own solution. I was using text fields inmy main table, converted to number, and problem solved when using subquery in the main query.
0
 
LVL 2

Author Closing Comment

by:RossDagley1
Comment Utility
problem resolved
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now