ACC2: How to Create a Multiple-Selection List Box (121356)


The information in this article applies to:

  • Microsoft Access 2.0
This article was previously published under Q121356

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.
NOTE: If you manually requery the list box by using the Requery macro action or the Requery method, or by pressing the F9 key in the list box, all the selections in the list box will be cleared.

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.
Modification Type:MajorLast Reviewed:11/6/2000
Keywords:kbhowto kbusage KB121356
©2002 Microsoft Corporation. All rights reserved.