Ad-Hoc Filtering of Subform

[Guest]

I am setting up a form/subform with companies on the main form and contact
people within companies on the subform. Each form is built on a query and
linked by the company id.

I want the users to be able to create ad-hoc filters for the forms using the
toolbar buttons but while it seems to work in my initial testing on the main
form, I cannot seem to get it to work on the subform. The selection comes up
but I cannot click on it.

Filter by form works on the main form but will not work on the subform.
Filter by selection works on both but on the subform, it will still show
other forms, but without any contacts if there are no contacts. eg. I
filtered by selection for someone with the last name of Reece. Only one
company has a contact with the name Reece. But both company records were
able to be displayed while only one showed the contact information for Reece.
When I get 500 records in here, this will not work because they would still
have to scroll through all the records to find anyone with that last name.
Ideally the filter by form and allowing the user to select Reece as the last
name should only show companies with someone named Reece but that is not
happening either, it only works on the main form.

I suspect it might have something to do with the programming I used to open
the form in a locked, view only state. The user clicks on an Edit button to
activate the controls and allow editing and a Save button to save the record
and reenter the locked control state. The switch to edit mode just
enumerates all controls and setts the locked property to true and activates
some buttons for use. I just don't know why this would affect the subform if
it does not affect the main form.

Any ideas are greatly appreciated.

 

[Rick Brandt]

I am setting up a form/subform with companies on the main form and
contact people within companies on the subform. Each form is built
on a query and linked by the company id.

I want the users to be able to create ad-hoc filters for the forms
using the toolbar buttons but while it seems to work in my initial
testing on the main form, I cannot seem to get it to work on the
subform. The selection comes up but I cannot click on it.

Filter by form works on the main form but will not work on the
subform. Filter by selection works on both but on the subform, it
will still show other forms, but without any contacts if there are no
contacts. eg. I filtered by selection for someone with the last name
of Reece. Only one company has a contact with the name Reece. But
both company records were able to be displayed while only one showed
the contact information for Reece. When I get 500 records in here,
this will not work because they would still have to scroll through
all the records to find anyone with that last name. Ideally the
filter by form and allowing the user to select Reece as the last name
should only show companies with someone named Reece but that is not
happening either, it only works on the main form.

I suspect it might have something to do with the programming I used
to open the form in a locked, view only state. The user clicks on an
Edit button to activate the controls and allow editing and a Save
button to save the record and reenter the locked control state. The
switch to edit mode just enumerates all controls and setts the locked
property to true and activates some buttons for use. I just don't
know why this would affect the subform if it does not affect the main
form.

Any ideas are greatly appreciated.

You have to understand that when you filter the subform you are not specifying
"show me main records that have sub-records matching this filter".

What you ARE specifying is "of the sub-records you would ordinarily show for
each main record, hide all but those matching this filter".

To limit the main records you see requires a main form filter. A subform filter
will never do that. You can provide that functionality with VBA code, but not
with the built in filtering provided by Access.

One way is to apply a main form filter that uses a sub-query. Something like...

CompanyID In(SELECT CompanyID FROM Contacts WHERE ContactName = "Reece")

A filter like that applied to the main form would show only companies that had
at least one contact named "Reece".

 

[Guest]

A conceptual problem at my end. Thank you for the code example. I will try
it.