Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

restricting query to results based on record in another table

Posted on 2011-02-11
8
Medium Priority
?
564 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:RossDagley1
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 2

Author Comment

by:RossDagley1
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:RossDagley1
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:RossDagley1
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:
RossDagley1 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:RossDagley1
ID: 34904661
problem resolved
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

722 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