Version 1.1           Approx 0.8 MB (zipped)                 First Published 22 Jan 2024                 Last Updated 29 Jan 2024


This is the second article in my series showing how functionality can be added to continuous forms.
In this example, filtered columns are automatically highlighted using very little code and the filter criteria are also displayed.

When no filtering is applied, the form looks like this:


The next screenshot shows the data filtered by Surname.
The Surname column is highlighted and the filter criteria, [Surname]="Bailey", is displayed in the header section


Click the Clear Filters button to restore the original data and remove the highlighting.

Next the form is filtered by Year Group and Tutor Group.
The filter criteria display is updated to [YearGroup]=8 AND [TutorGroup]="ES"


Next the form is filtered on 6 columns in different ways, including a multiselect filter on Year Group and wildcards on Surname and Forename.


Very little code is required to achieve these effects.

Clicking the Clear Filters button removes any existing highlighting by resetting the backcolor of each field control to white.

Private Sub ClearFilterFormat()

On Error GoTo Err_Handler

      'Used to clear filtered column highlighting
      'Reset default colour to white
      For Each ctl In Me.Controls
            ctl.BackColor = vbWhite
      Next

      Me.lblFilter.Caption = ""

Exit_Handler:
      Exit Sub

Err_Handler:
      If Err = 438 Then Resume Next  'error for controls that have no backcolor property e.g. line
      MsgBox "Error " & Err.Number & " in ClearFilterFormat procedure : " & Err.Description
      Resume Exit_Handler
End Sub

When any filtering is applied, the form current event runs the CheckFilterFormat procedure.

This clears the current highlighting using ClearFilterFormat then highlights the columns used in the current filter

Private Sub CheckFilterFormat()

On Error GoTo Err_Handler

      'Used to highlight filtered columns

      Dim strText As String, strFilter As String, strFormName As String
      strText = ""

      'Reset default colour to white
      ClearFilterFormat

      'Set filtered columns to pale green
      If Me.FilterOn = True Then strText = Me.Filter

      For Each ctl In Me.Controls
            If strText Like "*" & ctl.Name & "*" Then ctl.BackColor = 10092500   'pale green
      Next

      'display shortened version of filter criteria
      strFormName = "[" & Me.Name & "]."
      strFilter = Replace(strText, strFormName, "")
      strFilter = Replace(Replace(strFilter, "(", ""), ")", "")
      If strText <> "" Then Me.lblFilter.Caption = "Filter criteria: " & vbCrLf & strFilter

Exit_Handler:
      Exit Sub

Err_Handler:
      If Err = 438 Then Resume Next   'error for controls that have no backcolor property e.g. line
      MsgBox "Error " & Err.Number & " in CheckFilterFormat procedure : " & Err.Description
      Resume Exit_Handler

End Sub

The above code also displays a simplified version of the form filter criteria. The form name and its brackets are omitted from the displayed filter string.

For example, using the filter criteria:
      (((([frmStudentsCont].[YearGroup] Not In (10,11)) AND ([frmStudentsCont].[TutorGroup]="BS") AND ([frmStudentsCont].[Gender]="M")))
            AND ([frmStudentsCont].[Surname] Like "B*")) AND ([frmStudentsCont].[Forename] Like "*n")

This is simplified to the following for display purposes
      [YearGroup] Not In 10,11 AND [TutorGroup]="BS" AND [Gender]="M" AND [Surname] Like "B*" AND [Forename] Like "*n"

The form also includes code used in my Multiselect Filter example app to allow columns to be filtered using by the column header or by double clicking on the field control. For further details and all code, see my article: Streamline Multiselect Filter Code in Continuous Forms



Download

Click to download:   Highlight Filtered Columns v1.1     ACCDB file     Approx 0.8 MB (zipped)



Related Article

A similar approach is used in my Student Target Grade Explorer example app where combo box selections are used to help guide the filter process.
In that example, columns can also be sorted by clicking on the column header caption.




More Continuous Form Examples

The following articles provide further examples of functionality added to continuous forms:

•   Highlight Current Record
•   Highlight Selected Control or Column
•   Add Multiselect Filter   (2 pages)
•   Paint Me Transparent       (Selectively Hide Controls)
•   Sort columns
•   Hide duplicate values in a column   (as for reports)
•   Hide & Restore Selected Columns   (2 pages)
•   Freeze Columns   (3 pages)
•   Move and Resize Columns   (2 pages)



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 29 Jan 2024

Return to Example Databases Page

Return to Top