Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

restricting query to results based on record in another table

Posted on 2011-02-11
8
Medium Priority
?
566 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:Ross
  • 6
8 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 34869794
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 85
ID: 34869796
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:Ross
ID: 34869987
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 2

Author Comment

by:Ross
ID: 34870277
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
 
LVL 2

Author Comment

by:Ross
ID: 34870366
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:Ross
ID: 34870383
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:
Ross earned 0 total points
ID: 34870968
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:Ross
ID: 34904661
problem resolved
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

926 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