Solved

restricting query to results based on record in another table

Posted on 2011-02-11
8
553 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
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 84
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
 
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access creating a Chart from Search result on Form view 2 21
ms/access ftp / SFTP 3 32
Unrecognized Database Format 8 90
Search Form not Querying 2 10
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

914 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

22 Experts available now in Live!

Get 1:1 Help Now