Apply Filter to a Form based on Criteria

Logic

application background

This example shows property names that can be setup in Word by Access to keep track of variable information. Properties are attractive way to keep information updated since changing the value of a property changes all the places in the document it is used when the document is updated. Another common way to make substitutions for variable information is using bookmarks, but bookmarks just make one substitution atr a time. Properties update all the places a variable is used when the document is updated.

A bookmark just changes one place at a time, and properties do them all (nice! smile).

In this databse, Each property name must be unique. For code to implement uniqueness in a friendly way, see Unique Values using Access Form

mainform has controls to filter subform

On the mainform are controls for crieria to filter what shows in the subform below

filter subform based on user criteria

In this example:

the Category can be Custom (1="C"), Built-in (2="B") or All (3=No filter). This is collected using Option Buttons in an Option Group frame.
A checkbox is used to filter for having a value or not. The checkbox control has Triple State on the Data tab of the Property Sheet set to Yes so the value can be null so it's ignored when processing criteria.
Data Type has specific and general types. Access is more concerned with data type than Word is. General data types are in uppercase at the top of the list.
Pattern is whatever you want to look for in the property name string. It can have wildcards or not. What you enter will be prefaced and concluded with "*"

As criteria is specified and applied, the list gets shorter

As criteria is applied, there are less records in the subform

Goto Top

VBA

'*************** Code Start *****************************************************
' code: cmd_ApplyFilter_Click
'-------------------------------------------------------------------------------
' Purpose  : VBA for a command button Click event to apply a filter to a form
'              this example applies the filter to a subform
' Author   : crystal (strive4peace)
' Code List: www.MsAccessGurus.com/code.htm
' This code: https://msaccessgurus.com/presentation/Handouts_WordAutomation_s4p.zip
' LICENSE  :
'   You may freely use and share this code, but not sell it.
'   Keep attribution. Use at your own risk.
'-------------------------------------------------------------------------------

   
Private Sub cmd_ApplyFilter_Click() 
'221101...09, 221123 comment

   'dimension variables
   Dim sText As String _ 
      ,vWhere As Variant 
      
   'initialize variables
   vWhere = Null 
   
   'construct WHERE clause
   'sText is temporarily used in multiple places
   
   '---------------- Category is Built-in, Custom
   'construct criteria value for Cat
   Select Case Me.fltr_BC 
      Case 1: sText =  "C" 'custom
      Case 2: sText =  "B" 'built-in
      Case Else: sText =  "" 'not specified
   End Select 
   
   If sText <>  "" Then 
      'even though this is the first criteria for now,
      'consider that order might be changed for better performance
      vWhere = (vWhere +  " AND ") _ 
         &  "(Cat='" & sText &  "')"
   End If 
   
   '---------------- Data Type
   'construct full criteria clause to add
   '     uses = or IN
   sText =  ""
               
   With Me.fltr_DataTypi 
      If Not IsNull(.Value) Then 
         Select Case .Value 
         Case -10,-8,-1  'TEXT, DATE, YES/NO
             sText =  "DataTypi = " & Abs(.Value) 
         Case -9  'NUMBER - byte, int, long, cur, sgl, dbl
            sText =  "DataTypi IN (2,3,4,5,6,7)"
          Case 1 To 10  'mostly Standard data types
            sText =  "DataTypi = " & .Value 
         End Select 
      End If 
   End With 
   
   If sText <>  "" Then 
      vWhere = (vWhere +  " AND ") _ 
         &  "(" & sText &  ")"
   End If 
   
   '---------------- Pattern
   With Me.fltr_Pattern 
      If Not IsNull(.Value) Then 
         'problem if value has a double quote "
         'replace one double quote with 2
         ' use 2 double quotes inside string delimited with "
         vWhere = (vWhere +  " AND ") _ 
            &  "(PropName LIKE ""*" _ 
               & Replace(.Value, """", """""") _ 
               &  "*"")"
      End If 
   End With 
   
   '---------------- HasValue
   With Me.fltr_HasValue 
      If Not IsNull(.Value) Then 
         vWhere = (vWhere +  " AND ") _ 
            &  "(ActiveValue Is " _ 
            & IIf(.Value, "Not ", "") _ 
            &  "Null )"
      End If 
   End With 
   
   '---------------- ChangedValue
   With Me.fltr_ChangedValue 
      If Not IsNull(.Value) Then 
         vWhere = (vWhere +  " AND ") _ 
            &  "(NewValue " _ 
            & IIf(.Value, " <>0)", " =0)") 
      End If 
   End With 
   
   '----------------
   'apply filter to subform
   '  or show all records
   
   With Me.dm_f_PropertyList.Form  'form to apply filter
      If Not IsNull(vWhere) Then 
         .Filter = vWhere 
         .FilterOn = True 
      Else 
         .FilterOn = False 
      End If 
   End With 
   
End Sub 
'*************** Code End *******************************************************

Code was generated with colors using the free Color Code add-in for Access.

Goto Top

Reference

Form.Filter property

Form.FilterOn property (Access)

Download

Download text file with code: vba_cmd_ApplyFilter_Click__TXT.zip

If you have trouble with the download, you may need to unblock the ZIP file, aka remove Mark of the Web, before extracting the BAS file. Here are steps to do that: https://msaccessgurus.com/MOTW_Unblock.htm

Goto Top

Backstory

Giving a user capabilities to filter what they see so they can find what they're looking for faster is good to do.

If you like this page, please let me know, thank you. Donations are always appreciated

Share with others

Here's the link for this page in case you want to copy it and share it with someone:

https://msaccessgurus.com/VBA/Form_Filter.htm

or in old browsers:
http://www.msaccessgurus.com/VBA/Form_Filter.htm

Get Help with Access so you can do it yourself

Let's connect and team-develop your application together. I teach you how to do it yourself. My goal is to empower you.

While we build something great together, I'll pull in code and features from my vast libraries as needed, cutting out lots of development time. I'll give you lots of links to good resources.

Data structure is most important to get right. Users also need clear messages. Do you want your application to be better? I'd love to help you. Email me at training@msAccessGurus.com

~ crystal

the simplest way is best, but usually the hardest to see

Goto Top

Filter Form for User Criteria

Quick Jump

Logic

application background

mainform has controls to filter subform

VBA

Reference

Download

Backstory

Share with others

Get Help with Access so you can do it yourself