Filtering Forms…

Here are three ways to filter records on a Single Form or a Continuous Form. (In these examples no Subforms were used.)

Example 1
To toggle back and forth between (Yes/No Data Type) All and Completed (Tasks, Projects, Orders, etc.)…

• Step 1

In the Header of you Form place an UNBOUND Check Box and name it chkMyFilter

• Step 2

In the After_Update event of the UNBOUND Check Box copy/paste everything between the Private Sub and End Sub lines below. After you have copied it change pDone to the name of your Yes/No field in your Table, not the name of the field Control name on the Form.

Private Sub chkMyFilter_AfterUpdate()

    If Me.chkMyFilter = True Then
       Me.Filter = "pDone = " & vbTrue
       Me.FilterOn = True
   Else
      Me.Filter = ""
       Me.FilterOn = False
   End If

End Sub

Close and save (or save and then close) and try it out.

Example 2
In this example you can use values from a look-up table to filter your records. This allows a lot more flexibility because I can add/edit and/or subtract items from the list via the table. My table is…

tblSources

• Step 1

In the Header of you Form place an UNBOUND Combo Box and name it cboSSourceID. (The double S is not a mistake, it is because in the Detail section of my Form I have a Combo Box named cboSourceID, so, I put an additional S to indicate this is my cbo*Search*SourceID used to Filter my records.

• Step 2

Set the Row Source of cboSSourceID the same as the Combo Box in the Detail section of your Form. For this example I used…

SELECT tblSources.sSourceID, tblSources.sSource
FROM tblSources
WHERE (((tblSources.sActive)=True))
ORDER BY tblSources.sSortOrder;

Other settings:

Column Count =2
Bound Column = 1
Column Widths – 0″;1″

• Step 3

In the After_Update event of the UNBOUND Combo Box copy/paste everything between the Private Sub and End Sub lines below. After you have copied it change pSourceID to the field name in your Table, not the name of the field control name on the Form and in your Table and change the cboSSourceID to the name of your UNBOUND Combo Box in the Header.

Private Sub cboSSourceID_AfterUpdate()

   Me.Filter = "[pSourceID] = " & Me.cboSSourceID
   Me.FilterOn = True

End Sub

• • Step 3a

To clear the Filter you can use the Double_Click of cboSSourceID…

Private Sub cboSSourceID_DblClick(Cancel As Integer)

   Me.Filter = ""
   Me.FilterOn = False
   Me.cboSSourceID = ""

End Sub

…or add the above code a Clear Filter Command Button.

Close and save (or save and then close) and try it out.

Example 3
This example allows you to create a Query as much or as little criteria as you like for the filter. You can use one Command Button to toggle back and forth between All/Filtered, for this example I am using one Command Button using the Caption property of the Command Button to toggle back and forth.

• Step 1

Create two Record Sources, one that will show all the Records, such as the Table and another that shows just the filtered Records such as a Query, i.e.:

tblPending for all the Records
qryPending for the filtered Records.

qryPending
SELECT tblPending.pPendingID, tblPending.pActionID, tblPending.pQuantity, tblPending. pReceiveDate, etc…
FROM tblPending
WHERE (((tblPending.pActionID) Is Null) AND ((tblPending.pReceiveDate) Is Null));

• Step 2

Set the Record Source of your Form using the *filtered* query. In this example that would be qryPending. (If you want to open to showing ALL records you will need to reverse the code!)

• Step 3

Place a Command Button in your Forms Header and name it cmdShowAll. If the Wizard pops up, just press Cancel, we are going to put our own code there so we won’t be using any of the Wizards code. Change the Caption property to say “SHOW ALL”. For my Command button I used these settings…

Font Name= Tahoma
Font Size = 7
Font Weight = Bold

Side note:  The buttons will change Caption depending on which Record Source is showing, which is why you only need to add one Command Button.  Make sure you follow Step 4 and Step 5 for the Caption to toggle properly.

• Step 4

At the top of your Forms Module under…

Option Compare Database
Option Explicit

Side note: If those two lines are not at the top of your Module put them there, particularly, Option Explicit which will *stop* when it finds an undeclared variable(s).

Mine says SHOW ALL or COMPLETED ONLY but yours can say whatever you want. Just remember to make sure the Command Button is wide enough to show the longest Caption.

…place these two lines

Const ShowAll = "SHOW ALL"
Const ShowFiltered = "COMPLETED ONLY"

• Step 5

In the On_Click event of the Command Button copy/paste everything between the Private Sub and End Sub lines below.

Private Sub cmdShowAll_Click()

On Error Resume Next  'Lazy mans error code

   Select Case Me.cmdShowAll.Caption
       Case ShowFiltered
           Me.RecordSource = "qryPending"
           Me.cmdShowAll.Caption = ShowAll
       Case ShowAll
           Me.RecordSource = "tblPending"
           Me.cmdShowAll.Caption = ShowFiltered
   End Select

End Sub

Close and save (or save and then close) and try it out.

NOTE: When filtering a Subform use…

On Error Resume Next  'Lazy mans error code
      Me.NameOfSubform.Form.Filter ="[FieldBeingSearchedOn] = '" & Me!txtYourSearchTextBox & "'"
      Me.NameOfSubform.Form.FilterOn = True

Here are more filtering examples…

Allen Browne’s Search Form
Allen Browne’s Filter a Form on a Field in a Subform

 14,189 total views,  1 views today