Filter multiple subforms with unbound combo box

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?
LudiqueAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
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
 
jgoedersCommented:
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
 
LudiqueAuthor Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
LudiqueAuthor Commented:
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
 
jgoedersCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
jgoedersCommented:
and it will fail for your all case
0
 
Rey Obrero (Capricorn1)Commented:
not if you use the correct row source for the combo..
0
 
jgoedersCommented:
which would have a wildcard?
0
 
jgoedersCommented:
care to provide an example...it would be of great benefit to me in the future?
0
 
LudiqueAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
sorry the union should be

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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.