Solved

Filter multiple subforms with unbound combo box

Posted on 2008-06-21
12
1,753 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 500 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

733 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