ACC2: How to Create a Multiple-Selection List Box

• Microsoft Access version 2.0

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

In a typical list box, you can select only one item at a time. This article describes how to create a multiple-selection list box in which you can select more than one item at a time.

NOTE: Microsoft Access version 7.0 has incorporated the MultiSelect property for list box controls. The MultiSelect property of a list box specifies whether a user can make multiple selections in a list box and how the multiple selections can be made.

The following example demonstrates how to create a two-column list box in Microsoft Access version 2.0. The first column displays an "X" if the row is selected, or is blank if the row is not selected. The second column will contain the values that you can select. This article also demonstrates how to create a semicolon-delimited list of the items selected in the list box.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x, or the "Building Applications" manual in version 2.0.

MORE INFORMATION

The following example demonstrates how to create and use a multiple- selection list box. This example uses user-defined Access Basic functions to fill the list box.

1. Start Microsoft Access and open the sample database NWIND.MDB.

2. Create a new module and type the following lines in the Declarations section.

   NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

         Option Explicit
   

         Type MultiSelectArray_TYPE
            Selected As String     ' Holds "X" or "" indicating selection
            Display As Variant     ' The value to display in the list box
                                   ' row. Example: John Smith
            Value As Variant       ' The value to store for the row
                                   ' selection. Example: 535-86-9328 (John's
                                   ' SSN)
         End Type
   
         Dim MultiSelectArray() As MultiSelectArray_TYPE
         Dim MultiSelectRows
   
         ' Flag indicating if the list is being updated (new selection)
         ' or being filled.
         Global UpdateMultiSelect
   
   

3. Add the following two functions to the module:

         Function MultiSelect (fld As Control, id As Long, Row As _
         Long, Col As Long, Code As Integer)
         '*******************************************************
         ' CALLED FROM: The RowSourceType property of a list box.
         ' EXAMPLE:
         '    RowSourceType: MultiSelect
         '*******************************************************
            Dim RetVal: RetVal = Null
   
            Select Case Code
               Case LB_INITIALIZE
                  ' Is the list being updated by a new selection?
                  If UpdateMultiSelect Then
                     ' If so, ignore refilling the list.
                     UpdateMultiSelect = False
                  Else
                     ' Otherwise, fill the MultiSelect array.
                     MultiSelectRows = MultiSelectFillArray()
                  End If
                  RetVal = MultiSelectRows
   
               Case LB_OPEN
                  RetVal = Timer     ' Unique ID number for control.
   
               Case LB_GETROWCOUNT
                  ' Return the number of rows in the MultiSelect array.
                  RetVal = UBound(MultiSelectArray) + 1
   
               Case LB_GETCOLUMNCOUNT
                  ' Return the number of columns to display.
                  RetVal = 2
   
               Case LB_GETCOLUMNWIDTH
                  RetVal = -1        ' Use the default width.
   
               Case LB_GETVALUE
                  Select Case Col
                     Case 0  ' Selected
                        RetVal = MultiSelectArray(Row).Selected
                     Case 1  ' Display
                        RetVal = MultiSelectArray(Row).Display
                  End Select
   
               Case LB_END           ' End
   
            End Select
   
            MultiSelect = RetVal
   
         End Function
   
         Function MultiSelectUpdate (C As Control)
         '********************************************************
         ' CALLED FROM: The AfterUpdate property of the list box.
         ' EXAMPLE:
         '    AfterUpdate: =MultiSelectUpdate([<YourListBoxName>])
         '********************************************************
            ' Update the MultiSelect array selection by toggling
            ' the "X" in the selected row.
            Select Case MultiSelectArray(C).Selected
               Case ""
                  MultiSelectArray(C).Selected = "X"
               Case "X"
                  MultiSelectArray(C).Selected = ""
            End Select
   
            ' Set the flag indicating an update.
            UpdateMultiSelect = True
   
            ' Requery the list.
            C.Requery
   
         End Function
   
   

4. Create a user-defined function called MultiSelectFillArray() that will fill the MultiSelect array with the values to be displayed in the list box. Following are two examples of the MultiSelectFillArray() function. The first example demonstrates how to fill the array with a list of names from the Employees table. The second example demonstrates how to fill the array with a list of field names from the Employees table. Enter only one of these functions in the module:

         Function MultiSelectFillArray ()
         '**********************************************************
         ' PURPOSE: Fills the MultiSelect array with a list of
         '          names from the Employees table.
         ' CALLED FROM: The MultiSelect() function's initialization
         '              code to fill the list box array with values.
         '**********************************************************
            Dim DB As Database
            Dim RS As Recordset
            Dim i As Integer
            Dim RecordCount As Integer
   
            Set DB = DBEngine.Workspaces(0).Databases(0)
            Set RS = DB.OpenRecordset("Employees", DB_OPEN_SNAPSHOT)
   
            ' Get record count.
            RS.MoveLast
            RecordCount = RS.RecordCount
            RS.MoveFirst
   
            ' Resize the MultiSelect array to the number of Employee
            ' records.
            ReDim MultiSelectArray(0 To RecordCount - 1)
   
            ' Fill the MultiSelect array by setting:
            '    Selected to "" (clearing "X").
            '    Display to [First Name] space [Last Name].
            '    Value to [Employee ID].
            For i = 0 To RecordCount - 1
               MultiSelectArray(i).Selected = ""
               MultiSelectArray(i).Display = RS![First Name] & " " _
               & RS![Last Name]
               MultiSelectArray(i).Value = RS![Employee ID]
               RS.MoveNext
            Next i
   
            ' Return the number of rows in the array (RecordCount).
            MultiSelectFillArray = RecordCount
   
         End Function
   
         Function MultiSelectFillArray ()
         '**********************************************************
         ' PURPOSE: Fills the MultiSelect array with a list of
         '          field names from the Employees table.
         ' CALLED FROM: The MultiSelect() function's initialization
         '              code to fill the list box array with values.
         '**********************************************************
            Dim DB As Database
            Dim RS As Recordset
            Dim i As Integer
   
            Set DB = DBEngine.Workspaces(0).Databases(0)
            Set RS = DB.OpenRecordset("Employees", DB_OPEN_SNAPSHOT)
   
            ' Resize the MultiSelect array to the number of Employee
            ' fields.
            ReDim MultiSelectArray(0 To RS.Fields.Count - 1)
   
            ' Fill the MultiSelect array by setting:
            '    Selected to "" (clearing "X").
            '    Display to the name of the field.
            '    Value to the name of the field.
            For i = 0 To RS.Fields.Count - 1
               MultiSelectArray(i).Selected = ""
               MultiSelectArray(i).Display = RS(i).Name
               MultiSelectArray(i).Value = RS(i).Name
            Next i
   
            ' Return the number of rows in the array (the number of
            ' fields).
            MultiSelectFillArray = RS.Fields.Count
   
         End Function
   
   

5. Create a form not based on any table or query.

6. Add a list box with the following properties to the form:

         Name: EmployeeFields
         ControlSource: <blank>
         RowSourceType: MultiSelect
         RowSource: <blank>
         ColumnCount: 2
         ColumnWidths: 0.15 in.
         BoundColumn: 0
         Width: 1.6 in
         Height: 1.5 in
         AfterUpdate: =MultiSelectUpdate([EmployeeFields])
   

7. View the form in Form view. Depending on which example you chose in step 4, you will see a list box containing either a list of names from the Employees table or a list of field names from the Employees table. You can select or unselect an item in the list box by clicking the item. An item is selected if an "X" appears in the column to the left of the item. Note that if you press the UP ARROW and DOWN ARROW keys to move the pointer up or down in the list box, each item that you move past is toggled.

How to Create a Semicolon-Delimited List of Items Selected in List Box

The following example demonstrates how to use the MultiSelect array to create a semicolon-delimited list of the items selected in the list box:

1. Add the following function to the module that you created in step 2 above:

         Function MultiSelectSemicolonList ()
            Dim i
            Dim Result
            Result = ""
            For i = 0 To UBound(MultiSelectArray)
               If MultiSelectArray(i).Selected = "X" Then
                  Result = Result & MultiSelectArray(i).Display & "; "
               End If
            Next i
   
            ' Remove the last semicolon.
            If Result <> "" Then Result = Left(Result, Len(Result) - 1)
   
            MultiSelectSemicolonList = Result
         End Function
   
   

2. Add a text box with the following property to the form that you created in step 5 above:

         ControlSource: =MultiSelectSemicolonList()
   

3. View the form in Form view. Note that when you make selections in the list box, the text box is updated to display a list of the items selected.

REFERENCES

For more information about using an Access Basic function to fill a list box, search for "filling list boxes/combo boxes," and then "Filling a List Box or Combo Box Using an Access Basic Function" using the Microsoft Access version 2.0 Help menu.

For more information about MultiSelect list boxes in Microsoft Access version 7.0, search for "MultiSelect Property" using the Microsoft Access for Windows 95 Help Index.