Filter Form through sub-form values

[Robert Neville]

I would like to add filter functionality to my database whether
through the Main form or the subform. This question may be
rudimentary, yet I have not less experience with filtering data
outside from queries. Let me just add that Allen Browne excellent
article about this subject may not apply to this scenario on an
elementary level. (Here's the link to the article; Filter a Form on a
Field in a Subform - http://users.bigpond.net.au/abrowne1/ser-28.html)

Here's the scenario on my company form with an address subform
(one-to-many). A company could have multiple addresses and an address
could have many companies. The address subform list addresses in
single form view. The main company form has the tblComp as the record
source; address subform has a query based record source with
trelCompAddr and tblAddr. The address table also holds address data
for my project and contact tables. The linking table, trelCompAddress
contains phone number and email data.

I would like to filter the form (not by query) by area code.
Normally, I would create a query with tblComp, trelCompAddr, and
tblAddr; and set the phone field criteria to Like 212*. I would like
to filter company form with this area code and scroll through the
record subset using the Main Form. In addition, the main company form
and subform should remain editable at any time.

This feat seems more ambition if using the Access Advance Filter
dialog. Does it mean using the "On Filter" event to change the main
forms record source? Or Does one the "Apply Filter" event? "On
Filter" vs. "Apply Filter"? How would you change back the record
source when removing the filter? Both the main form and subform
should remain editable with the applied filter. Let me know if
someone could elaborate more on filtering forms through sub-form
values. Code Examples would help as well. How does this approach deal
with the other Access default filter option like Filter by Form?

 

[Ken Snell]

When you set a filter on a form, you are not changing the RecordSource of
that form. You are just using a filter on the form. This filter string is
held in the form's Filter property. You can turn a filter on or off by
setting the .FilterOn property to True or False respectively.