Solved

restricting query to results based on record in another table

Posted on 2011-02-11
8
562 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

627 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