Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1761
  • Last Modified:

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?
0
Ludique
Asked:
Ludique
  • 5
  • 4
  • 3
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
Rey Obrero (Capricorn1)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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now