Toggle Filter in multiple subforms

[John M]

Got a problem the group might be able to help with .

We have an Access Form with two subforms showing one table each. The user
can do a side by side comparison of the two tables. We have a status field
in both tables that gives
Status=0 -> match;
Status>=1 -> mismatch

Since we aren't interested in the 0s we set a filter Status>0 in both
subforms. To date this has all worked.

However the user now wants to upgrade the app. He needs to toggle the filter
off and on - both subforms at concurrently

I thought this would be easy. I just added the filter from the subform to
the VBA code in in a toggle command.

However the toggle command refuses to play ball. When you press it only the
subform DirListA toggles. The other refuses to release the filter and stays
filtered. You can manually select the B subform and then remove the filter.
However after that the toggle works to reapply the filter and then does
nothing again.

I assumed this was a focus issue and I've played around with that to no
avail.

Any thoughts?


------------------------------------------------------------
Private Sub cmdPopulate
''' ...code that gets data. This all works. The following is new code to
set the filter

bToggle = True

Me.DirListA.Form.Filter = "Status>0"
Me.DirListA.Form.FilterOn = True
Me.DirListB.Form.Filter = "Status>0"
Me.DirListB.Form.FilterOn = True

Me.Refresh
DoCmd.Hourglass False
End Sub

Private Sub cmdFilterToggle_Click()
'If Me.DirListA.Form.FilterOn = True Then '<------ orig used this property
to check (rather than boolean) but guessed this might be a focus issue
If bToggle = True Then
Me.DirListA.Form.FilterOn = False
Me.DirListB.Form.FilterOn = False

bToggle = False
Else
Me.DirListA.Form.Filter = "Status>0"
Me.DirListA.Form.FilterOn = True

Me.DirListB.Form.Filter = "Status>0"
Me.DirListB.Form.FilterOn = True

bToggle = True
End If
End Sub
----------------------------------------

 

[Jeanette Cunningham]

Hi John,
try removing all filters from the subforms.
Use the code on the toggle to build and set the filter for each subform.
That way the filter is controlled completely by the toggle.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John M]

Thanks for your response

I've checked every subform (both via the main form and through the
individual subforms and made doubly sure that no filter exists. I even
checked that the subform select statement contained no where clause.

No joy - the B form resolutely fails to toggle.

Interestingly enough. If I comment out the lines in the toggle statement
that refer to DirListA then B does toggle.

Hi John,
try removing all filters from the subforms.
Use the code on the toggle to build and set the filter for each subform.
That way the filter is controlled completely by the toggle.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John M]

Then I solved it.

Rather than switch the Filter Off I just set a filter that would invariably
be true.

This does the job.

Thanks again for your response


----------------------------------------

If bToggle = True Then
Me.DirListA.Form.Filter = "Status>-999"
Me.DirListA.Form.FilterOn = True

Me.DirListB.Form.Filter = "Status>-999"
Me.DirListB.Form.FilterOn = True
bToggle = False
Else
Me.DirListA.Form.Filter = "Status>0"
Me.DirListA.Form.FilterOn = True

Me.DirListB.Form.Filter = "Status>0"
Me.DirListB.Form.FilterOn = True

bToggle = True
End If
End Sub
----------------------------------------

Thanks for your response

I've checked every subform (both via the main form and through the
individual subforms and made doubly sure that no filter exists. I even
checked that the subform select statement contained no where clause.

No joy - the B form resolutely fails to toggle.

Interestingly enough. If I comment out the lines in the toggle statement
that refer to DirListA then B does toggle.