Preserving a Form's Filter

[cefrancke]

I have a form (no underlying record set) that has two separate
sub-forms on it.
Each sub-form has data from two different tables.
Above each sub-form there is one unbound combo box with a SQL record
source that returns an ID field and a Text field.
Next to each combo box is two buttons, one that 'Applys' the filter of
the sub-form to the value in the combo box and one that 'Clears' the
form's current filter.
When the click event of the "Apply Filter" button fires the following
code executes.....

[Subform1].Form.Filter = "[Subform1_FilterField] = " & Combo1.Value
[Subform1].Form.FilterOn = True

This causes Subform1 to show all records that match the Combo1 value.
Works fine.

When the click event of the "Clear Filter" button fires the following
code executes.....

[Subform1].Form.FilterOn = False
or
[Subform1].Form.Filter = ""

This causes Subform1 to show all records.
Works fine.

The same behavior happens with the second subform, combo box and
buttons. Just a different record set. Everything ok.

The problem is when I 'filter' the first subform and 'filter' the
second subform and then 'clear' anyone of the filters by clicking a
"Clear Filter" button, both subforms show all records.
The expected behavior is to have only one subform to show all records,
the one whose "Clear Filter" button was clicked, not both subforms!

Another funny thing, during trouble shooting, is, if Subform1 is
filtered and Subform2 "Clear Filter" button is clicked, nothing happens
to Subform1 as expected.

So it would seem that you have to apply both filters before the act of
clearing one is able to clear both.

Note, in my experience this happens to all subforms (even more than 2)
on the main form.
I need to have the other subforms remain in their filtered state.

So the question is, is there a way to clear a subform's filter without
clearing other subforms' currently applied filters?
I hope this was clear enough.
TIA

[Allen Browne]

There are major flaws in the way Access handles the Filter and OrderBy
properties of forms with subforms. I assure you the problem is with Access
and not with you.

For more info on other ways that these bugs manifest, see:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html
The article demonstrates four bugs, not exactly the same as yours, but the
same kinds of issue.

The best workaround might be to set the RecordSource of (one of) the
subforms instead of using filters. Unfortunately, when you do that Access
will decide to reassign the LinkMasterFields/LinkChildFields propeties of
the subform control to whatever it feels like, so you will probably have to
reassign those properties as well to get a consistent result.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ce*******@yahoo.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...
[snip]

The problem is when I 'filter' the first subform and 'filter' the
second subform and then 'clear' anyone of the filters by clicking a
"Clear Filter" button, both subforms show all records.
The expected behavior is to have only one subform to show all records,
the one whose "Clear Filter" button was clicked, not both subforms!

Another funny thing, during trouble shooting, is, if Subform1 is
filtered and Subform2 "Clear Filter" button is clicked, nothing happens
to Subform1 as expected.

So it would seem that you have to apply both filters before the act of
clearing one is able to clear both.

Note, in my experience this happens to all subforms (even more than 2)
on the main form.
I need to have the other subforms remain in their filtered state.

So the question is, is there a way to clear a subform's filter without
clearing other subforms' currently applied filters?

[cefrancke]

Thanks mate,

I just set the filter to 'True', which results in all records returned.
It's what I want, with the only exception that the record counter says
'(filtered)' after the numbers. It's not a real problem, but I dont
want users to think that there may be more records available. I'll have
to take what I can get.
Cheers!