Microsoft KB Archive/97518

{|
 * width="100%"|

ACC1x: How to Move List Box Selections to a Second List Box

 * }

Q97518

-

The information in this article applies to:


 * Microsoft Access versions 1.0, 1.1

-

SUMMARY
This article describes how to create a pair of list boxes to imitate the multiple selection behavior used in Microsoft Access wizards.

MORE INFORMATION
The example below demonstrates how to create a new form that contains two list boxes and three command buttons. You can add items to or delete items from the list using the command buttons or the properties of the list boxes:

  Create the following new table:

     Table: Table1 ---     Field Name: List (primary key) DataType: Text Field Size: 15 Caption: Items that will be provided in list Field Name: Selected DataType: Text Field Size: 5 Caption: Indicates if the item has been selected  View the table in Datasheet view. Add five records to the table. For each record, type any text in the List field (for example a list of colors or cities), and type &quot;YES&quot; (without quotation marks) in the Selected field.  Create the following two new queries based on Table1:

     Query: Select Yes ---     Field Name: List Show: Yes Criteria: [selected] = &quot;YES&quot;

Query: Select No     -- Field Name: List Show: Yes Criteria: [selected] = &quot;NO&quot;   Enter the following three sample functions in a new or existing module:      '======================================================= 'The following function opens the table and changes the 'selected value from YES to NO, and then executes the 'query for the two list boxes so that they will display 'the updated values. '=======================================================     Function ADD Dim MyDB As Database Dim MyTable As Table Dim y As Control

Set MyDB = CurrentDB Set MyTable = MyDB.OpenTable(&quot;Table1&quot;) Set y = forms!SelectList!field0

MyTable.index = &quot;PrimaryKey&quot; MyTable.Seek &quot;=&quot;, y

MyTable.Edit MyTable.[selected] = &quot;no&quot; MyTable.Update

MyTable.Close

DoCmd Requery &quot;field0&quot; DoCmd Requery &quot;field1&quot; End Function

'=======================================================     'The following function opens the table and changes the 'selected value from NO to YES, and then executes the 'query for the two list boxes so that they will display 'the updated values. '=======================================================     Function Del Dim MyDB As Database Dim MyTable As Table Dim y As Control

Set MyDB = CurrentDB Set MyTable = MyDB.OpenTable(&quot;Table1&quot;) Set y = forms!SelectList.field1

MyTable.index = &quot;primarykey&quot; MyTable.Seek &quot;=&quot;, y        MyTable.Edit MyTable.[selected] = &quot;yes&quot; MyTable.Update MyTable.Close

DoCmd Requery &quot;field0&quot; DoCmd Requery &quot;field1&quot; End Function

'=======================================================     'The following function sets all values in the Selected 'field to YES, and then executes the query for the two 'list boxes so that they will display the updated 'values. '=======================================================     Function Clear Dim MyDB As Database Dim MyTable As Table

Set MyDB = CurrentDB Set MyTable = MyDB.OpenTable(&quot;Table1&quot;)

On Error GoTo erhandle MyTable.MoveFirst

Do Until MyTable.EOF MyTable.Edit MyTable.[selected] = &quot;yes&quot; MyTable.Update MyTable.MoveNext Loop

erhandle: Resume Next

DoCmd Requery &quot;field0&quot; DoCmd Requery &quot;field1&quot; End Function   Create the following new, unbound form, and then save it as SelectList:

     Object: list box -     ControlName: field0 ControlSource: RowSourceType: Table/Query RowSource: Select Yes OnDblClick: =ADD

Object: list box -     ControlName: field1 ControlSource: RowSourceType: Table/Query RowSource: Select No        OnDblClick: =Del

Object: command button ---     ControlName: Button One Caption: Clear OnPush: =Clear

Object: command button ---     ControlName: Button Two Caption: Add item OnPush: =Add

Object: command button -     ControlName: Button Three Caption: Delete item OnPush: =Del 

The first list box, Field0, displays the items to be selected. The second list box, Field1, displays the items that are selected.