Version 1.1           Approx 1.2 MB                 First Published 23 Dec 2023                 Last Updated 14 Jan 2024


Section Links (this page):
          Introduction
          Using the Ribbon
          Using Code
          Further Info
          Download
          Video
          Summary
          More Continuous Form Examples
          Feedback

1.   Introduction                                                                                                                   Return To Top

      This is the third article in my series showing how functionality can be added to continuous forms. This article is in two parts.

      Recently I have been involved in two separate email exchanges with experienced developers wanting to have the same multiselect filter feature in
      continuous forms as is available in datasheet forms.

      In a datasheet form, two methods of filtering are available:

a)   Right click context menu on any column


b)   Left click on the down arrow in the column header


      In a continuous form, the right click context menu is similar including various filter options:


      However, the left click multiselect filter option is not provided on the form.

      In fact, this functionality is available but does not appear to be widely known by many experienced developers.

      This article will explain how the multiselect filter feature can easily be achieved in a continuous form, either from the user interface or using VBA code.


2.   Using the ribbon                                                                                                           Return To Top

      The Home ribbon includes a variety of sort and filter options:


      Select a column in the continuous form, then click the large Filter button to see the multiselect filter option


      The other ribbon filter options replicate the right click context menu.

      NOTE:
      This also works for checkbox and combobox controls in continuous forms.
      In fact it works all types of form including continuous subforms, datasheets, split forms and even single forms.

      This simple method is hiding in plain sight.
      However, recent email exchanges suggest it may have been forgotten by many experienced developers who minimize the ribbon by default.
      Ironically it may be better known by new users of Access who tend to accept the default user interface showing the ribbon.


3.   Using Code                                                                                                                     Return To Top

      If preferred, the same outcome is also easy to achieve using vode. Either of the following code lines work:

      DoCmd.RunCommand acCmdFilterMenu

      Application.CommandBars.ExecuteMso ("FiltersMenu")

      To make the functionality as similar as possible to datasheet forms, apply the code to the column header click event. First set focus to the field being filtered.

Private Sub Forename_Label_Click()

      Me.Forename.SetFocus
      DoCmd.RunCommand acCmdFilterMenu

End Sub

      Alternatively apply the code to the field control itself.
      I prefer to use the control's double click event for this otherwise the multiselect filter will appear whenever you click a control to edit data.

Private Sub Forename_DblClick(Cancel As Integer)

      Application.CommandBars.ExecuteMso ("FiltersMenu")

End Sub

      Each of these will result in the same sort/filter menu including the multiselect filter option:



4.   Further Info                                                                                                                   Return To Top

a)   In some cases such as primary key fields and date fields with a large number of unique values, the multiselect filter may NOT appear


      This also applies to the same fields in datasheet forms. The cause is a setting in Access Options which limits the number of items displayed in a list:


      Increasing this value to a larger number will allow the multiselect option to appear for all fields. However, if the value is too large, performance may be affected.

b)   Although both code methods normally work, I have experienced issues using the CommandBars.ExecuteMso code line in subforms.
      In my tests, error -2147467259 occurs when this code is used in subforms but not when the same form is used as a standalone form.


      To avoid this issue, just use the DoCmd.RunCommand code instead as this works in both forms and subforms.

c)   By default, this apporach will not work for linked ODBC tables, either in continuous forms or datasheets.
      To enable this feature, you need to go to Access Options . . . Current Database and tick the ODBC fields checkbox:


      You should be aware that doing this may also have a significant effect on performance, especially if using a larger list limit for ODBC tables with many records.

d)   In both datasheets and continuous forms, only one multiselect filter may be used at any time whether you use the ribbon or code.
      Use a different method of filtering if you need to filter several fields at the same time

5.   Download                                                                                                                       Return To Top

      Click to download the example app:   Multiselect Filter Continuous Form     1.2 MB (ACCDB - zipped)


6.   Video                                                                                                                               Return To Top

      I have created a video (12:04) to demonstrate both of these approaches.
      You can watch the Multiselect Filter in a Continuous Form video on my Isladogs YouTube channel or you can click below:

     

      If you liked the video, please subscribe to my Isladogs on Access channel on YouTube. Thanks.

      NOTE:
      A recent email exchange with fellow Access developer, Ben Sacherich, was one of the prompts for this article.
      Ben has created his own YouTube video on the multiselect feature: Filter an Access Form without Typing


7.   Summary                                                                                                                           Return To Top

      This article shows how a multiselect filter feature can easily be added to a continuous form, either from the user interface or using VBA code.

      The second article in this series will demonstrate several methods of streamliing the code using generic functions to add multiselect filters to any continuous form.



9.   More Continuous Form Examples                                                                                       Return To Top

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

      •   Highlight Current Record
      •   Highlight Selected Control or Column
      •   Highlight Filtered Columns
      •   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)


9.   Feedback                                                                                                                           Return To Top

      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 14 Jan 2024



Return to Example Databases Page 1 of 2 1 2 Return To Top