?
Solved

Filter multiple subforms with unbound combo box

Posted on 2008-06-21
12
Medium Priority
?
1,756 Views
Last Modified: 2013-11-28
I have a form with 10 little subforms.  The form has an unbound combo box call cboStaffID Each of the forms contains a field called StaffID.  

1) When I select a member of staff in the main form, I'd like all of the subforms to be filtered so that their StaffID is the same as the one selected?

2) I'd also like to be able to see all records in all subforms - so how do I include 'All' in the main form combo box?
0
Comment
Question by:Ludique
[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
  • 5
  • 4
  • 3
12 Comments
 
LVL 2

Expert Comment

by:jgoeders
ID: 21839673
me.subform1.recordsource = "Select * from ... where StaffID = " & cboStaffID
me.subform1.requery

There are a lot of things to have an all.  You could add an extra column to the cbo and have the rowsource something like this:

select 0, 0, 'All' FROM whatever UNION SELECT 1, ID, description from StaffIDTable

Hide the first two columns.  The first column is 0 if it's all and 1 if it's a regular selection.  Then just alter your code accordingly.  

The above code would become:

if me.cboStaffID = 0 then
me.subform1.recordsource = "Select * from ...
else
me.subform1.recordsource = "Select * from ... where StaffID = " & cboStaffID.column(1)
end if
0
 

Author Comment

by:Ludique
ID: 21839716
My code says this (I'll get to part 2 once I've sussed this bit)

Me.frmTODOContactReviews.RecordSource = "Select * from TODOContactReviews where AdviserID = " & cboStaffID
Me.frmTODOContactReviews.Requery

the subform is called frmTODOContactReviews and it's based on a query called TODOContactReviews

but when I compile the code it says "Invalid Outside Procedure"
0
 

Author Comment

by:Ludique
ID: 21839818
I've changed the bit at the beginning as follows:

frmTODO.ContactReviews.frmTODOContactReviews.RecordSource = "Select * from TODOContactReviews where AdviserID = " & cboStaffID

but it hasn't helped when I compile the code it still says "Invalid Outside Procedure
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 2

Expert Comment

by:jgoeders
ID: 21840626
i'm guessing the cbo is on the parent form, which is where the code is as well.  Try this:

me.frmTODOContactReviews.Form.RecordSource = "Select * from TODOContactReviews where AdviserID = " & cboStaffID
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21840746
Ludique,
why go thru all those codes-- if the record source of the subforms have a relation to the StaffID

just use the ** link child fields / link master fields ** properties of the subforms to filter the records based on what was selected from cboStaffID
0
 
LVL 2

Expert Comment

by:jgoeders
ID: 21840774
and it will fail for your all case
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21840821
not if you use the correct row source for the combo..
0
 
LVL 2

Expert Comment

by:jgoeders
ID: 21840859
which would have a wildcard?
0
 
LVL 2

Expert Comment

by:jgoeders
ID: 21840860
care to provide an example...it would be of great benefit to me in the future?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 21840880
something like this

select '*', '<All>' from tblstaff
union
select id, staffName from tblstaff

or

select null, '<All>' from tblstaff
union
select id, staffName from tblstaff
0
 

Author Comment

by:Ludique
ID: 21841113
This makes a very nice list in the dropdown:

Select Null, '<All>' from tblstaff UNION Select StaffID, StaffInitials from tblstaff;

but I can't select anything from it
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21841536
sorry the union should be

select id, staffName from tblstaff
union
select '*', '<All>' from tblstaff

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.
Suggested Courses

765 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