Microsoft Access Guru
Home Code

VBA > Form > Set Form Filter

Set Form Filter to limit the records displayed. Specify criteria using the form Filter property. Turn the filter on or off using the form FilterOn property.

You can have a form in Access that shows all records, then filter it for whatever criteria is specified, and ignore criteria that is not. 

'*************** Code Start *****************************************************
' Purpose  : VBA to Process Criteria and Set Form Filter
' Author   : crystal (strive4peace) www.MsAccessGurus.com
' License  : below code
'---------------------------------------------------------------------------------------

'                              SetFormFilter
'---------------------------------------------------------------------------------------
' 
Private Sub SetFormFilter()
  'crystal (strive4peace)

   ' declare variable to use for criteria  
   Dim varFilter As Variant
   
   ' initialize criteria to be nothing   
   varFilter = Null

   ' reference the form that the code is behind
   With Me
   
      ' text is delimited with ' (can also use ")     
      If Not IsNull(.controlname_for_text) Then
         varFilter = (varFilter + " AND ") _
                     & "[TextFieldname]= '" & .controlname_for_text & "'"
      End If
         
      ' dates are delimited with #       
      If Not IsNull(.controlname_for_date) Then
         varFilter = (varFilter + " AND ") _
                     & "[DateFieldname]= #" & .controlname_for_date & "#"
      End If

      ' numbers are not delimited         
      If Not IsNull(.controlname_for_number) Then
         varFilter = (varFilter + " AND ") _
               & "[NumericFieldname]= " & .controlname_for_number 
      End If
            
      If Not IsNull(varFilter) Then
         ' if there is a filter, then apply it and turn filter on      
          .Filter = varFilter
          .FilterOn = True
      Else     
         ' no filter, turn filter off to show all records 
          .FilterOn = False
      End If        
            
   End With' Me   

End Sub

' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' LICENSE
'   You may freely use and share this code 
'     provided this license notice and comment lines are not changed;
'     code may be modified provided you clearly note your changes.
'   You may not sell this code alone, or as part of a collection,
'     without my handwritten permission.
'   All ownership rights reserved. Use at your own risk.  
'   ~ crystal (strive4peace)  www.MsAccessGurus.com
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Logic

"Me" refers to the form that this code is behind.

ControlName_StringCriteria, ControlName_DateCriteria, and ControlName_NumberCriteria are Names of unbound controls on the form to collect criteria.

TextFieldname, DateFieldname, and NumericFieldname are field names in the Record Source for the form.

Me

Inside the With Me block, all references prefaced with . (dot) mean they are on the form, or are a property or method of the form.

If your criteria controls are on another form, or the form you are filtering is another form, you can split the code into two With blocks, and change the form reference to something else such as:

Delimiters

Delimiters are used for data that is not a number. Quote marks are used to delimit text. In this example, the varFilter criteria string uses double quote marks (") as delimiters, so the delimiter used for the string value is a single quote mark ('). Dates are delimited with #.

the Filter variable

varFilter is a variant so it can be Null. It will hold the string you are building for each condition -- but if nothing is specified in the filter control (IsNull), then that addition to the filter string is skipped.

(varFilter + " AND ")

If something is already in the filter (it is not Null), the word AND will be added. The beauty of using the + operator is that nothing + something = nothing ... so if nothing is there then nothing is added before the new criteria.

Finally, when the filter string is done, it is applied to your form if it has something in it. If not, then all the records will be displayed. That means that as you flip through records, only records matching what you want to see will show.