Microsoft KB Archive/121356

= ACC2: How to Create a Multiple-Selection List Box =

Article ID: 121356

Article Last Modified on 11/6/2000

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



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.

 Start Microsoft Access and open the sample database NWIND.MDB.  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   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([]) '********************************************************        ' 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   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  Create a form not based on any table or query.  Add a list box with the following properties to the form:

<pre class="fixed_text">     Name: EmployeeFields ControlSource: RowSourceType: MultiSelect RowSource: ColumnCount: 2 ColumnWidths: 0.15 in. BoundColumn: 0 Width: 1.6 in     Height: 1.5 in      AfterUpdate: =MultiSelectUpdate([EmployeeFields]) </li> 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.</li></ol>

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:

<ol>  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 </li> Add a text box with the following property to the form that you created in step 5 above:

ControlSource: =MultiSelectSemicolonList</li> 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.</li></ol>

<div class="references_section">