Resetting subform filters

[Rémi]

Hi all.
I have a form with two unbound subforms. The main form has a combobox
whose selection I use to filter *both* subforms. Code called on after
update:

Private Sub cmbMyCombo_AfterUpdate()
If IsNull(cmbMyCombo) then _
cmbMyCombo=-1

if cmbMyCombo=-1 then
me.frmSubForm1.Form.FilterOn = False
me.frmSubForm2.Form.FilterOn = False
else
me.frmSubForm1.Form.Filter="myComboId=" & cmbMyCombo
me.frmSubForm1.Form.FilterOn = true
me.frmSubForm2.Form.Filter="myComboId=" & cmbMyCombo
me.frmSubForm2.Form.FilterOn = true
end if
End Sub


When cmbMyCombo has a value of -1, I want to display everything. I've
found, though, that resetting the subform's filter only works for the
first subform - the second doesn't get reset. I've also tried
Filter="", FilterOn=true, but I get the same thing.

The only thing that's worked for me so far is this:

if cmbMyCombo=-1 then
me.frmSubform1.Form.FilterOn = false
me.frmSubform2.Form.Filter= "myComboId<>-1"
me.frmSubform2.Form.FilterOn=true
else
....

Has anyone seen this? I'm using Access XP SP3.

Regards,
Remi.

 

[Damian S]

Hi Remi,

Are you requerying your subforms after clearing the filter? And what is the
recordsource for your subforms? Is the default recordsource filtering the
records unwittingly?

Damian.

 

[Rémi]

Not requerying - it isn't necessary when I change the filter, I
wouldn't expect it to be necessary here... (Am I wrong?) Another
thing to note is that the *first* subform's filter is properly reset.
If I reverse the order I set FilterOn=false to, frmSubForm2 will have
its' data properly reset while frmSubForm1 does not.

The recordsource on both subforms is set to a table - not a query or
SQL. No accidental filtering :-(

Remi.

 

[Damian S]

Hi again Remi,

No, a requery is generally not required when applying/removing filters, but
since your filter isn't working, I was wondering if you could force a requery
to see if it resolved your issue.

Disregard that, as I have tested here and reproduced your problem. Requery
doesn't fix it on its own, but if you reset the subforms recordsource and
then requery it your problem will be resolved... like this:

Me.SUBFORM1.Form.Filter = ""
Me.SUBFORM1.Form.FilterOn = False
Me.SUBFORM1.Form.RecordSource = "TABLE1"
Me.SUBFORM1.Requery

Me.SUBFORM2.Form.Filter = ""
Me.SUBFORM2.Form.FilterOn = False
Me.SUBFORM2.Form.RecordSource = "TABLE2"
Me.SUBFORM2.Requery

Must be an undocumented feature of Access... I tested on A2003.

Damian.

 

[Rémi]

Thanks.
I suppose its' that, or continuing to assign "invalid" criteria to my
filter - criteria that will return all records.

Regards,
Remi.