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.
- In a module set a reference to the Microsoft Office 8 Object Library
- Create a toolbar called "Test Toolbar".
- 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)
- Name the first button "Filter By Form" and set the on action property to "=TBFilterForm()"
- Name the second button "Close" and set the on action property to "=TBCloseFilter()"
- Add the code headed Toolbar Code to a module
- Add the code to the On Close and On Open events of the form
- 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 ********************
