Microsoft Office 2000/Visual Basic Programmer's Guide

Filtering Data in Access

To filter data in an Access form or report by using VBA, set the Filter and FilterOn properties of the form or report. The Filter property specifies the criteria on which to filter, and the FilterOn property activates the filter. The following procedure filters a subform based on the user's selection in a combo box.

Private Sub cboCategories_AfterUpdate()
   ' Filter subform after value in combo box is updated.
   Dim lngCategoryID As Long

   ' Store value selected in combo box.
   lngCategoryID = cboCategories.Value

   ' Filter subform.
   With sfmProducts.Form
      ' If value is anything but "(All)", filter on that value.
      If lngCategoryID > 0 Then
         .Filter = "[CategoryID]=" & lngCategoryID
         .FilterOn = True
      ' If value is "(All)", remove filter.
      Else
         .FilterOn = False
      End If
   End With
End Sub

This procedure appears in the frmProductsByCategory form module in the NorthwindReports.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH15 subfolder on the Office 2000 Developer CD-ROM.