Version 1.1 Approx 1.2 MB First Published 23 Dec 2023 Last Updated 14 Jan 2024
Section Links (this page):
Using the Ribbon
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.
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:
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()
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)
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.
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
8. 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. 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