Microsoft KB Archive/132137

= ACC2: Moving List Box Items from One List Box to Another =

Article ID: 132137

Article Last Modified on 11/6/2000

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q132137



SUMMARY
This article describes how you can create a form that has two list boxes that imitate the multiple-selection capability reflected in Microsoft Access Wizards.

This method uses a Yes/No field in the table to indicate which records are selected. One list box displays the Yes records and the other displays the No records. To move items from one list box to the other, the Yes/No field of the selected record is set to the appropriate state and the list boxes are then requeried to update their respective lists.

Note that the method provided is suitable for single-user environments only. If this method is used in a multiuser environment, what one user does may interfere with the actions of another. See the "References" section later in this article for more information on a multiuser technique.



MORE INFORMATION
In addition to the two list boxes, the form also contains three command buttons. By using the command buttons, you can add items to, or delete items from the list box. You can also double-click an item in a list box to move it to the other list box. To create the form, follow these steps:

  Open the sample database NWIND.MDB and create the following new table:

     Table: Table1 ---     Field Name: List Data Type: Text Field Size: 15 Caption: Items that will be provided in list Field Name: Selected Data Type: Text Field Size: 5 Caption: Indicates if the item has been selected

Table Properties: Table1 PrimaryKey: List  View the Table1 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 then type YES in the Selected field.  Create the following new query based on the Table1 table and save it as Select Yes:

     Query: Select Yes ---     Field: List Show: Yes Criteria: [selected] = "YES"   Create another new query based on the Table1 table as follows and save it as Select No:

     Query: Select No      -- Field: List Show: Yes Criteria: [selected] = "NO"  Create a new blank form and save it as SelectList.</li>  With the SelectList form open in Design view, click Code on the View menu, and then type the following line in the form module's Declarations section:

<pre class="fixed_text">      Option Explicit </li>  Type the following three functions in the form module:

<pre class="fixed_text">      '======================================================= ' The following function opens the table and changes the ' selected value from YES to NO, and then runs the ' query for the two list boxes so that they will display ' the updated values. '=======================================================      Function Add Dim MyDB As Database Dim MyTable As Recordset Dim y As Control

Set y = Me![Field0]

If IsNull(y) Then MsgBox "Please select something in the list." Else Set MyDB = DBEngine.Workspaces(0).Databases(0) Set MyTable = MyDB.OpenRecordset("Table1")

MyTable.Index = "PrimaryKey" MyTable.Seek "=", y

MyTable.Edit MyTable.[Selected] = "no" MyTable.Update

MyTable.Close Me![Field0].Requery Me![Field2].Requery End If      End Function

'=======================================================      ' The following function opens the table and changes the ' selected value from NO to YES, and then runs the ' query for the two list boxes so that they will display ' the updated values. '=======================================================

Function Del Dim MyDB As Database Dim MyTable As Recordset Dim y As Control

Set y = Me![Field2]

If IsNull(y) Then MsgBox "Please select something in the list." Else Set MyDB = DBEngine.Workspaces(0).Databases(0) Set MyTable = MyDB.OpenRecordset("Table1")

MyTable.Index = "PrimaryKey" MyTable.Seek "=", y

MyTable.Edit MyTable.[Selected] = "yes" MyTable.Update

MyTable.Close Me![Field0].Requery Me![Field2].Requery End If      End Function

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

Set MyDB = DBEngine.Workspaces(0).Databases(0) Set MyTable = MyDB.OpenRecordset("Table1")

On Error GoTo erhandle

MyTable.MoveFirst Do Until MyTable.EOF MyTable.Edit MyTable.[Selected] = "yes" MyTable.Update MyTable.MoveNext Loop

MyTable.Close Me![Field0].Requery Me![Field2].Requery

erhandle: Resume Next

End Function </li>  Save and close the form module. Then, add the following list box and command button controls to the SelectList form:

<pre class="fixed_text">     List Box: -     Name: Field0 RowSourceType: Table/Query RowSource: Select Yes OnDblClick: =Add

List Box: -     Name: Field2 RowSourceType: Table/Query RowSource: Select No        OnDblClick: =Del

Command Button: Name: Button One Caption: Clear OnClick: =Clear

Command Button: Name: Button Two Caption: Add Item OnClick: =Add

Command Button: ---     Name: Button Three Caption: Delete Item OnClick: =Del </li> View the SelectList form in Form view. Note that the first list box, Field0, displays all the items in Table1 you can select. You can use the Clear, Add Item, or Delete Item buttons to add or remove items from the second list box, Field2. You can also double-click an item in a list box to move it to the other list box.</li></ol>

<div class="references_section">