Clearing a filter on one subform incorrectly clears filter on another subform

[BAM65Y]

I'm using Access 2000 however I've had a similar problem
before using Acess 97.

I have an unbound form with 2 subforms. The first
subform has 2 linked fields to the main form. In
addition a combobox the main form is used to apply a
filter to the first subform. The oncurrent event in the
first subform sets a hidden field on the main form that
is used as the master link field for the second subform.
Another combobox on the main form is used to apply a
filter to the second subform.

When the form is opened and filters are applied to both
subforms (using the 2 combobox criteria fields on the
main form) everything works fine. If the second combobox
(which drives the filter for the second subform) is
cleared, the AfterUpdate event calls a subroutine to
update the filter for the second subform. The subroutine
will set the filter to "" and set the filteron = false
for the second subform using the following code: -

Me.frm_Test_Concerns_Subform.Form.Filter = ""
Me.frm_Test_Concerns_Subform.Form.FilterOn = false

Regardless of the order that I run these to commands (or
even if I only run one of them) not only does it set the
FilterOn property of the second subform (i.e
frm_Test_Concerns_Subform) to false, but it also
incorrectly sets the FilterOn property of the 1st subform
(called frm_Test_Stream_Phase_Factor_subform) to false

Has anyone encountered this problem before and is a
workaround or patch available?

 

[Allen Browne]

There is a bug in Access such that if you have filtered both the main form
and subform and then remove one of the filters, Access also sets the
FilterOn property to No for the other. It does not actually remove the
filter, but neither can you since the property has already (wrongly) been
set to No.

It sounds as if you may have stumbled onto a variation of this bug. AFAIK,
the bug has not been fixed.

As a workaround, would you be able to set the RecordSource of the subforms
instead of applying a filter?

 

[Marshall Barton]

I'm using Access 2000 however I've had a similar problem
before using Acess 97.

I have an unbound form with 2 subforms. The first
subform has 2 linked fields to the main form. In
addition a combobox the main form is used to apply a
filter to the first subform. The oncurrent event in the
first subform sets a hidden field on the main form that
is used as the master link field for the second subform.
Another combobox on the main form is used to apply a
filter to the second subform.

When the form is opened and filters are applied to both
subforms (using the 2 combobox criteria fields on the
main form) everything works fine. If the second combobox
(which drives the filter for the second subform) is
cleared, the AfterUpdate event calls a subroutine to
update the filter for the second subform. The subroutine
will set the filter to "" and set the filteron = false
for the second subform using the following code: -

Me.frm_Test_Concerns_Subform.Form.Filter = ""
Me.frm_Test_Concerns_Subform.Form.FilterOn = false

Regardless of the order that I run these to commands (or
even if I only run one of them) not only does it set the
FilterOn property of the second subform (i.e
frm_Test_Concerns_Subform) to false, but it also
incorrectly sets the FilterOn property of the 1st subform
(called frm_Test_Stream_Phase_Factor_subform) to false

Has anyone encountered this problem before and is a
workaround or patch available?

The bleeping Filter property strikes again. It's because of
this problem that I avoid the filter property whenever
possible. Actually the problem is even worse than that, if
the main form is also bound, setting or clearing either
subform or the main form's Filter will reset the other one's
Filter too.

The workaround is to use the tried and true technique from
the days before A95 foisted the abominable Filter on us poor
unsuspecting folks. Use code to reconstruct whichever
form's record source query with your filter in the Where
clause. If the query is complicated enough to make this
difficult, use an SQL statement based on the existing saved
query. The basic idea of the code to filter a form without
using the Filter property is along these lines:

Assuming the filter you want to use is somthing like
"somefield = " & Me.somecontrol

then setting the record source might be like:

Me.subform.Form.RecordSource = _
"SELECT * " & _
"FROM somequery " & _
"WHERE somefiled = " & Me.somecontrol