Setting MultiSelect At Runtime

Today I was working on a form with a list box.  I was using the form to gather user inputs for two different, but similar, processes.  For one of the processes, I wanted to allow the user to select multiple items in the list box.  For the other process, I wanted to limit the user to selecting only a single item.

It seemed easy enough.  I used the OpenArgs parameter of the DoCmd.OpenForm method to pass along my preference from the calling procedure:

'Process 1:
DoCmd.OpenForm "MyForm", OpenArgs:="AllowMultiSelect=True"

'Process 2:
DoCmd.OpenForm "MyForm", OpenArgs:="AllowMultiSelect=False"

In the form's Open event, I used my Parse function to extract the value of the AllowMultiSelect "property."  I then used that value to set the MultiSelect property of the list box:

Private Sub Form_Open(Cancel As Integer)
    Dim AllowMultiSelect As Boolean
    AllowMultiSelect = Parse(Me.OpenArgs, "AllowMultiSelect", vbBoolean)
    
    If AllowMultiSelect Then
        Me.MyListBox.MultiSelect = 2  'Extended multi-select via Ctl/Shift
    Else
        Me.MyListBox.MultiSelect = 0  'None (select one item at a time)
    End If
End Sub

There was one slight problem:

This property can be set only in form Design view.

That's right.  We can't actually set this property at runtime.  When I tried running the code above, I received the following error:

Run-time error '2448':

You can't assign a value to this object.

I had already made up my mind how I wanted the user interface to work, though.  It was time to find another way.

Workaround options

If you're stubborn enough, there's almost always another way to work around an obstacle in Access.  I immediately considered a few options:

1. Make two forms: one with a multi-select list box and another with single-select
2. Make two list boxes: hide one or the other when the form opens
3. Set Echo to False, open form in Design View, set MultiSelect property as needed, save and close form, set Echo to True, open form in Form View
4. Allow multi-select at design time and use the AfterUpdate event to emulate single-select at runtime as needed

I decided against Option 1 because the two forms would have been identical except for this one property.  That means twice the maintenance going forward.  No thanks.

Option 2 was actually a pretty good option.  I decided against it in this case, though, because of how I was designing my form.  I'm using my form generation classes, and they're not optimized for creating two controls that occupy the same space.  I could have done it, but it would have been ugly.

Option 3 is a technique that I have used in the past, but I typically use it only as a last resort.  I was going to exhaust all other possibilities before falling back on this approach.

Option 4 ended up being pretty straightforward to accomplish, so that's what I went with.

Emulating single-select at runtime

To implement the Option 4 solution, I started by setting the MultiSelect property to Simple or Extended in form design view.  The only requirement is that the list box's MultiSelect property be set to something besides None.

Then, I got rid of the code in the Form_Open event and replaced it with the code below in the list box's AfterUpdate event:

Private Sub MyListBox_AfterUpdate()
    Dim AllowMultiSelect As Boolean
    AllowMultiSelect = Parse(Me.OpenArgs, "AllowMultiSelect", vbBoolean)
    
    If Not AllowMultiSelect Then
        EmulateSingleSelect Me.MyListBox
    End If
End If

The Code

Below is the code for the EmulateSingleSelect routine.  The code is fairly straightforward except for one thing.  I had to add a workaround for what appears to be a bug in the implementation of the list box's Selected property.  If the list box's ColumnHeads property is set to True, then it seems to throw off the indexing of the Selected property.

When the ColumnHeads property of the list box is set to True, then Selected uses a 1-based index.  If ColumnHeads is False, then Selected has a 0-based index.  The documentation makes no mention of the difference in behavior.

I submitted a bug report via the File -> Feedback mechanism inside Access itself.  I'll see if I get a response.

'Used to implement a runtime-optional MultiSelect property for list boxes;
'   the MultiSelect property of list boxes can only be set at Design Time;
'   to simulate making this a runtime option, we can set the form control to
'   allow Simple/Extended multi-select and then call this routine when we
'   want the list box to behave as though "None" was the MultiSelect setting
'Note that we do not change the value of the current item as the user may
'   have just toggled its value to True or False
'Usage: Private Sub MyListBox_AfterUpdate()
'           If DenyMultiSelect Then ClearOthersInListBox MyListBox
'       End Sub
Sub EmulateSingleSelect(LBox As ListBox)
    Dim CurrentIndex As Long
    CurrentIndex = LBox.ListIndex

    Dim i As Long
    For i = 0 To LBox.ListCount - 1
        If LBox.ColumnHeads Then
            'There appears to be a bug in Access 
            '   with indexing of the Selected property
            '   when the ListBox ColumnHeads property is set to True
            If i <> CurrentIndex Then
                LBox.Selected(i + 1) = False
            End If
        Else
            If i <> CurrentIndex Then
                LBox.Selected(i) = False
            End If
        End If
    Next i
End Sub

Image by Dan Fador from Pixabay