Simple Filters

An easy-to-use form that applies either one or two filters to data on a form.

Introduction

In Access Archon #129(Fancy Filters), I described a form that allowed you to filter data using seven filters and two sorts.  Some readers found the complexity of this form overwhelming, so in response to their pleas, I have constructed a much simpler form that applies either one or two filters to data on a form, using a simpler technique to create and apply the filters.

The Sample Form

The sample database, Simple Filters.mdb (an Access 2000 database), has two tables:  tblSuppliers (from the Northwind sample database), and tblProcessingCost, a table that records processing costs for suppliers and dates.  The database has one form, frmProcessingCosts, shown below:

Figure B.  The frmProcessingCosts form as initially opened.

When the form is opened, it displays all the processing cost records, sorted first by company name (ascending) and then by date (descending), so that the most recent date for each company is listed first.  Note that the All Records option is selected in the Records option group.

To filter the records, click the Filtered Records option; this enables the Filter by Date and Filter by Company combo boxes, where you can select a date, a company, or both.  After making your selection(s), click the Create and Apply Combined Filter command button to create the filter programmatically, and write it to the form’s Filter property; clicking the Clear Combined Filter command button clears the filter and displays all records again.  Figure B shows the form with a date and company filter applied:

Figure C.  The frmProcessingCosts form with a combined filter applied.

VBA Code

All the code is in the frmProcessingCosts form module, but several public variables are declared in a standard module.  The relevant procedures from the form module are listed below.

Private Sub cboCompanyName_AfterUpdate()

On Error GoTo ErrorHandler

   pstrCompanyIDFilter = “[SupplierID] = ” & _

      Nz(Me![cboCompanyName].Value)

   Debug.Print “Company ID filter: ” & pstrCompanyIDFilter

   Me![txtFilter].Value = CreateFilter

ErrorHandlerExit:

   Exit Sub

ErrorHandler:

   MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description

   Resume ErrorHandlerExit

End Sub

Private Sub cboDate_AfterUpdate()

On Error GoTo ErrorHandler

   pstrDateFilter = “[CostDate] = ” & Chr$(35) _

      & Nz(Me![cboDate].Value) & Chr$(35)

   Debug.Print “Date filter: ” & pstrDateFilter

   Me![txtFilter].Value = CreateFilter

ErrorHandlerExit:

   Exit Sub

ErrorHandler:

   MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description

   Resume ErrorHandlerExit

End Sub

Private Sub fraRecordsFilter_AfterUpdate()

On Error GoTo ErrorHandler

   intChoice = Nz(Me![fraRecordsFilter].Value, 1)

   Select Case intChoice

      Case 1

All records

         Call ClearFilter

      Case 2

Allow filters

         Me![cboDate].Enabled = True

         Me![cboDate].Value = Null

         Me![cboCompanyName].Enabled = True

         Me![cboCompanyName].Value = Null

   End Select

ErrorHandlerExit:

   Exit Sub

ErrorHandler:

   MsgBox “Error No: ” & Err.Number & “; Description: ” & _

      Err.Description

   Resume ErrorHandlerExit

End Sub

Private Function CreateFilter() As String

On Error GoTo ErrorHandler

Concatenate filters

   pstrFilter = IIf(pstrDateFilter <> “”, pstrDateFilter & ” And “, “”) _

      & IIf(pstrCompanyIDFilter <> “”, pstrCompanyIDFilter, “”)

   Debug.Print “Filter: ” & pstrFilter

Strip out terminating ‘And’, if necessary

   If Right(pstrFilter, 5) = ” And ” Then

      pstrFilter = Left(pstrFilter, Len(pstrFilter) – 5)

      ‘Debug.Print “Filter: ” & pstrFilter

   End If

   CreateFilter = pstrFilter

ErrorHandlerExit:

   Exit Function

ErrorHandler:

   MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description

   Resume ErrorHandlerExit

End Function

Private Sub ApplyFilter()

On Error GoTo ErrorHandler

   Me.FilterOn = True

   Me.Filter = CreateFilter

   Me.Refresh

ErrorHandlerExit:

   Exit Sub

ErrorHandler:

   MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description

   Resume ErrorHandlerExit

End Sub

Private Sub ClearFilter()

On Error GoTo ErrorHandler

   Me![fraRecordsFilter].Value = 1

   pstrFilter = “”

   pstrDateFilter = “”

   pstrCompanyIDFilter = “”

   Me![txtFilter].Value = “”

   Me![cboDate].Enabled = False

   Me![cboDate].Value = Null

   Me![cboCompanyName].Enabled = False

   Me![cboCompanyName].Value = Null

   Me.Filter = “”

   Me.Refresh

ErrorHandlerExit:

   Exit Sub

ErrorHandler:

   MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description

   Resume ErrorHandlerExit

End Sub

References

The code in the sample database does not need any special references.

Supporting Files

The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site.  It is accarch160.zip, which is the last entry in the table of Access Archon columns for Access Watch.