Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter events (Access 2002)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter events (Access 2002)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter events (Access 2002)

    Thanks, Hans!

    Sometimes I think my brain isn't screwed all the way in. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter events (Access 2002)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •