Can't clear filter from multiple subforms with code.

[vircalendar via AccessMonster.com]

I have a form with one regular subform and then another subform that, in turn
has two subforms of its own. I can easily filter the three subforms using
code, but when I try to clear the filter using the code below, I run into
problems:

Forms![traffic_control]![frm_worklist]![approved_studies].Form.
FilterOn = False
Forms![traffic_control]![frm_worklist]![completed_studies].Form.
FilterOn = False
Forms![traffic_control]![pending_studies].Form.FilterOn = False

Here's what's wierd: if I run this code as-is, the two nested subform drop
their filter but the other subform doesn't. If I run it this way:

Forms![traffic_control]![pending_studies].Form.FilterOn = False
Forms![traffic_control]![frm_worklist]![approved_studies].Form.FilterOn
= False
Forms![traffic_control]![frm_worklist]![completed_studies].Form.
FilterOn = False

then the first subform cleard its filter but neither of the nested ones do.
In other words, I have my choice, but I can't clear them all. I've tried
setting the focus to either the parent form or to the nested subform (as
below), but it makes no difference.

Forms![traffic_control]![pending_studies].Form.FilterOn = False
Forms![traffic_control]![frm_worklist].setfocus
Forms![traffic_control]![frm_worklist]![approved_studies].Form.FilterOn
= False
Forms![traffic_control]![frm_worklist]![completed_studies].Form.
FilterOn = False

I've also tried adding filter = "" code for each of the subforms, but it
makes no difference. The only thing that really works is to right click, then
click on the button "remove filter/sort." However, I don't want the end user
to rely on this method. Is there a way to duplicate that functionality with
VBA code? Alternatively, can someone tell me what I'm doin wrong with the
code I have?

 

[Marshall Barton]

I have a form with one regular subform and then another subform that, in turn
has two subforms of its own. I can easily filter the three subforms using
code, but when I try to clear the filter using the code below, I run into
problems:

Forms![traffic_control]![frm_worklist]![approved_studies].Form.
FilterOn = False
Forms![traffic_control]![frm_worklist]![completed_studies].Form.
FilterOn = False
Forms![traffic_control]![pending_studies].Form.FilterOn = False

Here's what's wierd: if I run this code as-is, the two nested subform drop
their filter but the other subform doesn't. If I run it this way:

Forms![traffic_control]![pending_studies].Form.FilterOn = False
Forms![traffic_control]![frm_worklist]![approved_studies].Form.FilterOn
= False
Forms![traffic_control]![frm_worklist]![completed_studies].Form.
FilterOn = False

then the first subform cleard its filter but neither of the nested ones do.
In other words, I have my choice, but I can't clear them all. I've tried
setting the focus to either the parent form or to the nested subform (as
below), but it makes no difference.

Forms![traffic_control]![pending_studies].Form.FilterOn = False
Forms![traffic_control]![frm_worklist].setfocus
Forms![traffic_control]![frm_worklist]![approved_studies].Form.FilterOn
= False
Forms![traffic_control]![frm_worklist]![completed_studies].Form.
FilterOn = False

I've also tried adding filter = "" code for each of the subforms, but it
makes no difference. The only thing that really works is to right click, then
click on the button "remove filter/sort." However, I don't want the end user
to rely on this method. Is there a way to duplicate that functionality with
VBA code? Alternatively, can someone tell me what I'm doin wrong with the
code I have?

Because the filter property has been fraught with problems
since it was introduced in A95, many people won't use it.

The alternate approach is to reconstruct each form/subform's
record source SQL statement. This is usually not very
difficult. Your existing code probably looks something
like:
Dim stWhere As String
stWhere = "field=" & frm.field
frm.Filter = stWhere
frm.FilterOn = True

That would translate to the alternate like:
Const stSQL = "SELECT ... FROM ... "
Dim stWhere As String
stWhere = "field=" & frm.field
frm.RecordSource = stSQL & stWhere

I have not tested it throughly, but I have been assured that
the problems with the Filter property have been fixed in
A2007.

 

[vircalendar via AccessMonster.com]

Thanks.

I'm actually pulling the data from a query rather than an sql statement,
though I guess it's essentially the same thing.

I have a workaround now: rather than displaying all records by setting the
filter to null, I'm just changing it to "filteritem1 OR filteritem2 OR
filteritem3".

I have a form with one regular subform and then another subform that, in turn
has two subforms of its own. I can easily filter the three subforms using

[quoted text clipped - 34 lines]

VBA code? Alternatively, can someone tell me what I'm doin wrong with the
code I have?

Because the filter property has been fraught with problems
since it was introduced in A95, many people won't use it.

The alternate approach is to reconstruct each form/subform's
record source SQL statement. This is usually not very
difficult. Your existing code probably looks something
like:
Dim stWhere As String
stWhere = "field=" & frm.field
frm.Filter = stWhere
frm.FilterOn = True

That would translate to the alternate like:
Const stSQL = "SELECT ... FROM ... "
Dim stWhere As String
stWhere = "field=" & frm.field
frm.RecordSource = stSQL & stWhere

I have not tested it throughly, but I have been assured that
the problems with the Filter property have been fixed in
A2007.