Version 1.51           Approx 1.8 MB (zipped)                 First Published 16 Jan 2024                 Last Updated 29 Jan 2024


Section Links (this page):
          Introduction
          Download
          Class Module Button Code
          Class Module Position Code
          Label Caption Code
          Label Name Code
          Video
          Summary
          More Continuous Form Examples
          Feedback



1.   Introduction                                                                                                                   Return To Top

      In the first part of this article, I showed how you could easily add the multiselect filter functionality to continuous forms, either using the ribbon or using code.


      I provided event code to do this for both the field controls and the unassociated header labels.

      The code for each field control event is very simple as it just uses DoCmd.RunCommand acCmdFilterMenu

Private Sub Surname_DblClick(Cancel As Integer)

      DoCmd.RunCommand acCmdFilterMenu

End Sub

      For the field control I used the double click event so the field could be edited without the filter menu being displayed.

      The click event code for the header label needs one extra line to first set the focus to the field control

Private Sub Surname_Label_DblClick()

      Me.Surname.SetFocus
      DoCmd.RunCommand acCmdFilterMenu

End Sub

      Whilst the above code works perfectly, it does require each label click event to have the 'related' field control entered manually.

      It also results in a lot of almost identical code which is far from efficient.


      I was prompted to do something about this following a comment by @nutsonbikes to the Multiselect Filter in a Continuous Form video I created for my
      Isladogs YouTube channel. His(?) comment was:

      Soooo simple! I can't believe I haven't thought about looking for a solution like this.
      I ended up with a bunch of unbound combos and a concatenation routine to set and clear the form filter
      I bet you could even use an event handler function to keep the VBE cleaner.

      I decided to write generic event code that would avoid unnecessary repetition and could be used in any header label click event on any form.
      To do this, I need to know the name of the related field control in each case.

      That may sound easy to do. However, getting this information was surprisingly tricky for a number of reasons:

a)   Where 'child' labels are associated with 'parent' controls such as textboxes, the labels have no events.
      However, the name of either control can be determined using VBA. See my article Get Control Properties in Code
      As that isn't relevant in this case, I won't go into details here.

b)   For continuous forms, the header label is in a separate form section to the field controls.
      This means labels do have several events that can be utilised but also that they are not 'associated' with the field control.


      As a result, there is no parent/child control and no simple method of determining the label name from the field control (or vice versa)

      A lot of searching using Google/Bing eventually led to a class module solution for Excel user forms but this wasn't directly usable in Access.

      With suggestions from other experienced Access developers, I came up with four approaches that worked and allowed the code to be significantly streamlined.
      Each approach involved a comparison of the name, caption or position of the header label (or button) with that of the 'associated' textbox control being filtered.



2.   Download                                                                                                                        Return To Top

      The example app includes the code used for each approach. It has a main form giving access to eight other forms:


      The first four forms are the same as in the first page of this article, but with additional comments explaining the code.
      The final four forms use generic code which is explained in the following sections of this article. In each case, the form code is also far more streamlined.

      Click to download the example app:   MultiselectFilterContForm_v1.51     ACCDB file     Approx 1.8 MB   (zipped)



3.   Class Module Button Code                                                                                             Return To Top

      The starting point was the class module solution by Rick Rothstein for Excel user forms: Get the name of the label just clicked
      I sent the link to fellow Access developer, A.P.R. Pillai, who kindly modified it to work in Access.

      His solution involved placing transparent buttons over each header label.


      The button click events are used to obtain both the button name and from that the name of the related control.

      NOTE:
      This approach requires the buttons to have the same name as the field control with the prefix 'cmd' or 'btn'
      For example: cmdForename / Forename OR btnGender / Gender etc

      The form code is much simpler with code only in the Form_Load event:

Private lcmd As clsFilterMenu
Private col As New Collection

'==================================================

Private Sub Form_Load()

      Dim ctl As Control

      For Each ctl In Me.Controls

            Select Case TypeName(ctl)

            Case "CommandButton"
                  Set lcmd = New clsFilterMenu
                  Set lcmd.frm = Me
                  Set lcmd.cmdEvents = ctl
                  lcmd.cmdEvents.OnClick = "[Event Procedure]"
                  col.Add lcmd
                  Set lcmd = Nothing
            End Select

      Next

End Sub

      The Form_Load event runs code in a class module clsFilter

Public WithEvents cmdEvents As Access.CommandButton
Public frm As Access.Form

'======================================================

'Uses transparent buttons over the header labels
'cmdEvents_Click runs on Form_Load event and
'identifies the field control name based on the command button name

'Loosely based on code by Rick Rothstein for user forms at:
'https://answers.microsoft.com/en-us/msoffice/forum/all/get-the-name-of-the-label-just-clicked/81b557f5-7828-4a24-a7ca-e8cb9f44775b

'Modified by a.p.r. pillai for use in Access
'Minor changes by Colin Riddington
'======================================================

Sub cmdEvents_Click()

On Error GoTo Err_Handler

      Dim txt As String

      'get name of associated control - same as button minus the leading cmd
      'e.g. if button is cmdSurname => control name is Surname
      'This will fail if names do not match

      txt = Mid(cmdEvents.Name, 4)

      'set focus to control
      frm.Controls(txt).SetFocus

      'show multiselect filter menu (code in modFilter)
      ShowMultiselectMenu

Exit_Handler:
      Exit Sub

Err_Handler:
      'only needed where unrelated command buttons are used e.g. cmdClear / cmdClose
      'In such cases, error 2465 occurs as no control exists to take focus ... so Exit Sub
      If Err = 2465 Then Exit Sub

End Sub

      The result is that no code is needed on either the transparent buttons or the labels underneath


      The rest of the form code is run from the property sheet using functions:
      For example, each of the textbox double click events run the ShowMultiselectMenu function

Public Function ShowMultiselectMenu()
      DoCmd.RunCommand acCmdFilterMenu
End Function

      Running identical function code for several controls to the property sheet makes the form more streamlined and also makes it faster to assign the code.
      All the controls can be selected as a group and the function assigned to each control in the group in one step.


      NOTE:
      This method is relatively slow where there are a lot of records to include in the mutliselect list.
      You may see a short delay when you click on the transparent button over PupilID which has almost 1500 unique values



4.   Class Module Position Code                                                                                           Return To Top

      This approach was created by fellow MVP, Tom van Stiphout. In this case the textbox 'paired with' each label is determined by position.
      This requires the left position of each label / textbox 'pair' to be the same or within a defined limit.


      For this example, I chose to allow a variation of 1/4 inch (360 twips) but you can remove or adjust the amount of flexibility to suit your own purposes.

      In this case, the form code is even more concise:

Private m_clsForm As clsForm

'==================================================

Private Sub Form_Close()
      Set m_clsForm = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
      Set m_clsForm = New clsForm
End Sub

      This uses code in class modules clsForm and clsColumn to identify the field control name based on label & control left positions

      The clsColumn code is:

'Class module code by Tom Van Stiphout

Private Const MODULE_NAME As String = "clsForm"
Private Const EVENT_PROC As String = "[Event Procedure]"
Private WithEvents m_ctlLabel As Access.label
Private m_ctlData As Access.Control

'==================================================

Public Property Get ctlData() As Access.Control
      Set ctlData = m_ctlData
End Property

Public Property Set ctlData(ByVal objNewValue As Access.Control)
      Set m_ctlData = objNewValue
End Property

Public Property Get ctlLabel() As Access.label
      Set ctlLabel = m_ctlLabel
End Property

Public Property Set ctlLabel(ByVal objNewValue As Access.label)
      Set m_ctlLabel = objNewValue
      m_ctlLabel.OnClick = EVENT_PROC
End Property

Private Sub m_ctlLabel_Click()
      Const PROC_NAME As String = "m_ctlLabel_Click"
      ' Debug.Print Time$, MODULE_NAME, PROC_NAME, m_ctlLabel.Name, m_ctlData.Name

      m_ctlData.SetFocus
      RunCommand acCmdFilterMenu

End Sub

      The clsForm code is:

'Class module code by Tom Van Stiphout with minor modifications by Colin Riddington

Private Const EVENT_PROC As String = "[Event Procedure]"
Private WithEvents m_frm As Access.Form
Private columns As VBA.Collection 'of clsColumn objects.

'==================================================

Private Sub Class_Initialize()


Set columns = New VBA.Collection

On Error Resume Next

Set m_frm = CodeContextObject 'Split form: Error 2467: The expression you entered refers to an object that is closed or doesn't exist.

      If Err.Number = 0 Then
            If (m_frm.CurrentView = 1 And m_frm.DefaultView = 1) Then
                  Init
            Else
                  Debug.Assert False 'This is for Continuous forms.
            End If
      Else
            Debug.Assert False 'This is for Continuous forms.
      End If

End Sub

Private Sub Class_Terminate()
      Set m_frm = Nothing
End Sub

Private Sub Init()

On Error GoTo Err_Handler

      Dim ctl As Control
      Dim ctlSameColumn As Control
      Dim column As clsColumn

      For Each ctl In m_frm.Controls
            If ctl.ControlType = AcControlType.acLabel Then
                  If ctl.Section = 1 Then '1=Form Header
                        Set ctlSameColumn = GetSameColumnControl(ctl)
                        If ctlSameColumn Is Nothing Then
                              ' Debug.Print Time$, ctl.Name, "Unable to find SameColumnControl"
                  Else
                        Set column = New clsColumn
                        Set column.ctlLabel = ctl
                        Set column.ctlData = ctlSameColumn
                        columns.Add column, ctl.Name
                  End If
            End If
      End If
      Next ctl

Exit_Handler:
      Exit Sub

Err_Handler:
      MsgBox Err.Description & " ErrorNo: " & Err.Number
      Resume Exit_Handler
      Resume

End Sub

Private Function GetSameColumnControl(ByRef lbl As Access.label) As Access.Control

      Dim ctl As Control

      For Each ctl In m_frm.Controls
            If ctl.Section = 0 Then '0=acDetail
                  'Tom van Stiphout: use Size ... ToGrid and require exact alignment
                  ' If ctl.Visible = True And ctl.Left = lbl.Left Then
                  'Colin Riddington: allow some flexibility in control alignment e.g. 1/4 inch (0.635 cm) = 360 twips
                  If ctl.Visible = True And ctl.Left > (lbl.Left - 360) And ctl.Left < (lbl.Left + 360) Then
                        Set GetSameColumnControl = ctl
                  Exit For
            End If
      End If
      Next ctl

End Function

      All other form code is run from functions in the property sheet:
      a)   Control DblClick events run ShowMultiselectMenu function
      b)   Form footers set from function GetFormFooterCaptions in Form_Load
      c)   ClearFilter function runs from in cmdClear_Click

      Although there is now far more code in the class modules, do bear in mind that the code can be applied to any Access form in the same way



5.   Label Caption Code                                                                                                         Return To Top

      This approach was suggested as a possible solution by fellow MVP, Karl Donaubauer who referred me to an article by another MVP, Mike Wolfe:
            AccHitTest: Returns the Form Control the Mouse Clicked On

      In this case the textbox 'paired with' each label depends on the label caption being identical to the textbox control name.


      This method uses the relatively little known Accessibility method accHitTest to identify the object that has been clicked and use that to obtain its caption

      The code uses the function ShowCaptionMultiselectFilter in modFilter

Private Type POINTAPI
      x As Long
      y As Long
End Type

Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" _
      (ByRef lpPoint As POINTAPI) As Long

'==================================================

Function ShowCaptionMultiselectFilter(frm As Form)

'Colin Riddington
'This only works if the label caption is the same as the related field control

Dim pt As POINTAPI
Dim accObject As Object

      GetCursorPos pt
      Set accObject = frm.AccHitTest(pt.x, pt.y)
      If Not accObject Is Nothing Then
            frm(accObject.Caption).SetFocus
            DoCmd.RunCommand acCmdFilterMenu
      End If

End Function

      There is no code in the form module with all other code run from functions in the property sheet
      In fact the form code module is empty apart from comments used for explanation in this example app


      The form code module can be safely deleted.

      NOTE:
      Whilst preparing this article, I discovered a strange issue with the accessibility code where a dual monitor setup is used with the primary monitor on the right.


      The code works perfectly on the primary monitor.
      If the form is placed on the secondary monitor, the X-Y co-ordinates of the form and its controls are negative.

      The code then fails on the line Set accObject = frm.AccHitTest(pt.X, pt.Y)
      Using error handling this line returns error 5: Invalid procedure call or argument.

      Instead, if the left monitor is made the primary monitor, the position co-ordinates are positive on both monitors and the accHitTest code works on each.
      These findings have also been confirmed by three other experienced Access developers

      Surprisingly, it appears that the Accessibility code accHitTest cannot handle negative co-ordinates! This appears to be a bug.
      I will update both the article and/or code if and when I have further information about this issue.



6.   Label Name Code                                                                                                             Return To Top

      This is a variation on the previous approach
      In this case the textbox 'paired with' each label depends on the label name being similar to the textbox control name.


      For example, Surname_Label & Surname OR lblForename & Forename. Any naming convention can be used but it must be consistent for each label/textbox 'pair'

      This method also uses the accHitTest method to identify the object that has been clicked and use that to obtain its name


      The code uses the function ShowNameMultiselectFilter in modFilter

Private Type POINTAPI
      x As Long
      y As Long
End Type

Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" _
      (ByRef lpPoint As POINTAPI) As Long

'==================================================

Function ShowNameMultiselectFilter(frm As Form)
'Colin Riddington
'This only works if the related field control name can be derived from the label name

      Dim pt As POINTAPI
      Dim accObject As Object
      Dim strText As String

      GetCursorPos pt
      Set accObject = frm.AccHitTest(pt.x, pt.y)

      'The following assumes labels named using default Access method e.g. Surname_Label, Gender_Label
      strText = Left(accObject.Name, InStr(accObject.Name, "_") - 1)

      'Modify as necessary
      'e.g. if using e.g. lblSurname, lblGender etc ...
      'strText = Mid(accObject.Name, 4)

      If Not accObject Is Nothing Then
            frm(strText).SetFocus
            DoCmd.RunCommand acCmdFilterMenu
      End If

End Function

      NOTE:
      The code has the same issue with accHitTest on the secondary monitor of a dual monitor setup where the primary monitor is placed on the right.



7.   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.



8.   Summary                                                                                                                               Return To Top

      All of the above methods work but you should be aware of the issues / constraints involved with each approach

      a)   Using Class Module & Transparent buttons
            Form design needs to be modified to add buttons; buttons MUST have similar names to textbox controls; comparatively slow for larger recordsets

      b)   Using Class Module & Position Code
            The label & textbox control positions MUST be aligned within specified limits; requires two separate class modules

      c)   Using Accessibility Code & Label Captions
            Very concise code in a standard module; possible issues with accHitTest on secondary monitor; label captions MUST match textbox control names

      d)   Using Accessibility Code & Label Names
            Similar to c); labels MUST have similar names to textbox controls

      Each method also involved running much of the code as functions directly from the property sheet.
      Doing this results in 'lightweight' forms with little or no code

      However, moving code to the property sheet and away from the form can also make the functionality more obscure.
      Many experienced developers, including myself, prefer to keep almost all code as event procedures and largely avoid this approach.

      Mike Wolfe wrote an excellent article discussing the advantages and disadvantages of lightweight forms: Lightweight Forms? Just Don't

      None of the methods described above require you to use lightweight forms.
      Each method will work equally well with all function code run from form event procedures instead of the property sheet.



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)



10.   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 29 Jan 2024



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