Filtering a main form based on a sub-form

[Leif]

I've run into a number of cases where the user needs to filter main form
records based on the content of a sub-form. Is there a common (easy?) way to
accomplish this?

In the past I've created a new form joins the main form and sub-form
information, allowing the user to sort & filter in that form. I also tried
providing a query button where the user can enter subform information and I
generate a query to return the requested filtering.

Is there an easier way? I would love to me able to just right-click on a
subform field and enter criteria that would then filter records at the main
form level.

Thanks.

 

[Jeff Boyce]

Leif

I'm having trouble following what you want to have happen.

In a standard mainform/subform design, the main form displays the "one" side
of a one-to-many relationship, while the subform displays the "many" records
related to the one in the main form. I'm not understanding why you'd use
the subform to filter the mainform, rather than vice versa...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Leif]

Hi Jeff,

Let me give you an example. Suppose you have a main form that shows
employees. Also suppose that as part of that main form you have a sub-form
of projects. Lets suppose that the relationship between employess and
projects is 1 to many; an employee may have many projects but a project will
only be assigned to one employee.

As part of the project information you have a cost center. Lets say we have
the following data:

employee 1
project 1 cost center 10
project 2 cost center 11
project 3 cost center 10

employee 2
project 4 cost center 10
project 5 cost center 12
project 6 cost center 13

employee 3
project 7 cost center 14

Now, if I'm looking at employee 1 record I see three projects as part of a
sub-form. If I filter on "10" in the sub-form list I will now see two
projects (1 and 3).

However, this is not what I want. I want to be able to filter on cost
center "10" in the sub-form and instead have the filter apply to the main
form. In that case it will shown 2 employees that match (employee 1 & 2).
Employee 3 does not match since he does not have a project that is part of
cost center "10".

Regards,
Leif

 

[Jeff Boyce]

Leif

I'm not sure how I'd approach that if I were limited to doing it the way
you've described.

(but I'm not, so here's another possibility...)

In your main form, you could pick whether you were going to search by
employees, search by project or search by cost center. I'd use an option
group to make this selection, then change the SQL used to "fill" the
combobox that lets me select a main-form record (either a specific employee,
or a specific project, or a specific cost-center).

Of course, this approach would depend on how the data was structured.
Please describe a bit more about how your data is structured.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Joan Wild]

Allen Browne has what you need. look for 'Filter a form on a field in a
subform' at
http://allenbrowne.com/tips.html

Joan Wild
MS Access MVP

 

[Leif]

Jeff,

It looks like the answer to my question is that there is no "easy" way to do
what I'm looking to do. As I mentioned in the OP I've accomplished this task
in the past by:

1. Creating a new form with all fields linked with an inner join and
providing a datasheet view so that the user may filter using MS provided
short-cut menu filtering.

2. Provide a query form that changes the recordsource of the main form in
the same manner that is demonstrated by Allen (see Joan's link below).

I think in my current case I can get by with doing something a bit more
simple, using a combo box on cost center and having the user just use the
sort-cut menu filtering for other main form filters.

Thanks and Regards,
Leif

 

[Leif]

Thanks for the link.

Allen Browne has what you need. look for 'Filter a form on a field in a
subform' at
http://allenbrowne.com/tips.html

Joan Wild
MS Access MVP