Code Examples

Filter Records Using The Filter By Form Method

acCmdClearGrid, acCmdFilterByForm, acCmdApplyFilterSort, acCmdRemoveFilterSort, acCmdCloseWindow

The routines here allow you to display a form in the Filter By Form mode. There are a number of steps required to ensure this example works.

  1. In a module set a reference to the Microsoft Office 8 Object Library
  2. Create a toolbar called "Test Toolbar".
  3. Add 2 buttons to the toolbar in the following order. (These must be the first 2 buttons on the toolbar for this to work as coded)
    1. Name the first button "Filter By Form" and set the on action property to "=TBFilterForm()"
    2. Name the second button "Close" and set the on action property to "=TBCloseFilter()"
  4. Add the code headed Toolbar Code to a module
  5. Add the code to the On Close and On Open events of the form
  6. Change the Toolbar property of the form to "Test Toolbar"
'***************** Code Start *******************
' This code was originally written by Terry Wickenden.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.

'=========Toolbar Code=============

Function TBFilterForm()

  Dim strTest As String
  Dim ctl As CommandBarControl

  Set ctl = CommandBars("Test Toolbar").Controls(1)
  strTest = ctl.Caption
  Select Case strTest
    Case "Filter By Form"
      DoCmd.RunCommand acCmdFilterByForm
      'Remove any existing filter information
      DoCmd.RunCommand acCmdClearGrid
      ctl.Caption = "Apply Filter"
      'Show the close button on the toolbar
      CommandBars("Test Toolbar").Controls(2).Visible = True
    Case "Apply Filter"
      DoCmd.RunCommand acCmdApplyFilterSort
      ctl.Caption = "Remove Filter"
      'Hide the close button on the toolbar
      CommandBars("Test Toolbar").Controls(2).Visible = False
    Case "Remove Filter"
      DoCmd.RunCommand acCmdRemoveFilterSort
      ctl.Caption = "Filter By Form"
  End Select

         End Function

Function TBCloseFilter()

  On Error GoTo ErrCloseFilter

  'Reset the caption for the filter button
  CommandBars("Test Toolbar").Controls(1).Caption = "Filter By Form"
  'Hide the close button
  CommandBars("Test Toolbar").Controls(2).Visible = False

  DoCmd.RunCommand acCmdCloseWindow
  Exit Function

ErrCloseFilter:

  Select Case Err
    Case 2501
      ' When you close the Filter Window
      ' Access thinks that you have canceled
      ' an action - do nothing
    Case Else
      MsgBox Err.Number & ":-" & vbCrLf & Err.Description
  End Select

End Function

'====== End of Toolbar Code===========

'======== Form Code =============

Private Sub Form_Close()

  'Make sure that the Tool bar has  the correct caption
  CommandBars("Test Toolbar").Controls(1).Caption = "Filter By Form"
  'Display the close button on the toolbar - so it can be seen in design!!!
  CommandBars("Test Toolbar").Controls(2).Visible = True

End Sub

Private Sub Form_Open(Cancel As Integer)

  'Hide the close button on the toolbar
  CommandBars("Test Toolbar").Controls(2).Visible = False

End Sub

'====== End of Form Code ================

'****************** Code End ********************

© 1998 - 2011 Terry Wickenden TKW Design Site developed maintained and hosted by TKW Design. This site is best viewed at 1024 x 768. Optimised for Firefox.