Filter events (Access 2002)

Topic

New Reply

Hi All!!!

I’m working with filters and trying to figure out which events are triggered when.

My form has a subform and two tabs. The subform is a datasheet: If Tab1 is selected, then the Filter is [Inactive]=False, but if Tab2 is selected, then Filter is [Inactive]=True. This, alone, works great, because I use the change event of the tabs to change the content of the filter in the subform.

But when a user uses Filter by Selection on the form, all heck breaks loose. It works okay for the initial filter, but then when the user goes to Remove filter, then my little Inactive filters get messed up. What I have noticed is that while the ApplyFilter event of the subform does not get triggered when the user is filtering, the ApplyFilter event of the _main_ form gets triggered. So I tried doing this:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
If ApplyType = acShowAllRecords Then
resetMainFilter
End If
End Sub

(where resetMainFilter is the function to determine which tab is currently clicked and to set the filter based on the active tab).

But, alas, it does not work. I have confirmed that this event, as well as the applyType, are properly triggered, but the filter is not reset. Any suggestions?

TIA!

Here’s the function to reset the filter:
Sub resetMainFilter()
Dim strFilter As String

Select Case Me.tabApplStatus.Value
Case 0 ‘Active
strFilter = “[Inactive] = False”
Case 1 ‘Inactive
strFilter = “[Inactive] = True”
End Select

Forms!frmApplications.frmTrack.Form.Filter = strFilter
Forms!frmApplications.frmTrack.Form.FilterOn = True

Me.txtRecordCount.Requery

End Sub

Reply | Quote

Replies

I would change the record source of the subform in the On Change event of the tab control, instead of changing the filter. You could create two queries, one with criteria False for the Inactive field, and one with criteria True for the Inactive field, and set the record source to the name of the appropriate query. Or you can assemble an SQL string in code and use that as record source (either “SELECT * FROM … WHERE Inactive = False” or “SELECT * FROM … WHERE Inactive = True”). That way, you don’t have to worry about the user setting/removing filters.

Reply | Quote

Thanks, Hans!

Sometimes I think my brain isn’t screwed all the way in.

Reply | Quote

Don’t worry, this solution (as so many) is only obvious in hindsight!

Reply | Quote