How to use AddItem and RemoveItem to move selections from one list box to another (278378)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

When you use the List Box Wizard to select fields from a table or query, the third step in the process presents two lists: Available Fields and Selected Fields. The Selected Fields list is initially empty. You can select fields in one list and then move them to the other by clicking one of four buttons.

This article shows you how to create a form with similar lists that you can use to select particular records for further processing as you require. This can be useful, for example, when you need to select certain customers whose identities are known to the operator but for whom you cannot easily set criteria in a query.

To create a form that enables you to select customers in this way, follow these steps:

1. Start Access, and then open the sample database Northwind.mdb or the sample project NorthwindCS.adp.
2. Create a new form in Design view, and then save it with the name Select Customers.
3. Create two list boxes in the detail section of the form. Name one box List1 and the other box List2. Set their RowSourceType properties to Value List.
4. Create four command buttons, one below the other in the detail section of the form, and then set their properties as follows:

           Name                  Caption
        ----------              ----------
        cmdMoveToList2              >
        cmdMoveAllToList2          >>
        cmdMoveToList1              <
        cmdMoveAllToList1          <<     
   					

5. On the toolbar, click the Code button, and in the Code window, type or paste one of the following procedures, depending on whether you are using Northwind.mdb or NorthwindCS.adp:
   • If you are using Northwind.mdb, type or paste this procedure:

     Private Sub Form_Load()
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim strSQL As String, strItem As String
     
        strSQL = "SELECT CustomerID, CompanyName FROM Customers"
        Set db = CurrentDb
        Set rs = db.OpenRecordset(strSQL)
        Do Until rs.EOF
           strItem = rs.Fields("CustomerID").Value & ";" _
              & rs.Fields("CompanyName").Value
           Me.List1.AddItem strItem      ' Row Source Type must be Value List
           rs.MoveNext
     
        Loop
        rs.Close
        Set rs = Nothing
        Set db = Nothing
     End Sub
     						

   • If you are using NorthwindCS.adp, type or paste this procedure:

     Private Sub Form_Load()
     
        Dim cn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim strSQL As String, strItem As String
     
        strSQL = "SELECT CustomerID, CompanyName FROM Customers"
        Set cn = Application.CurrentProject.Connection
        Set rs = New ADODB.Recordset
        rs.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic
       
        Do Until rs.EOF
           strItem = rs.Fields("CustomerID").Value & ";" _
              & rs.Fields("CompanyName").Value
           Me.List1.AddItem strItem      ' Row Source Type must be Value List
           rs.MoveNext
        Loop
        rs.Close
        Set rs = Nothing
     
        Set cn = Nothing
     
     End Sub
     						

6. Add the following procedures for both Northwind.mdb or NorthwindCS.adp:

   Private Sub cmdMoveAllToList1_Click()
       MoveAllItems "List2", "List1"
   End Sub
   
   Private Sub cmdMoveAllToList2_Click()
       MoveAllItems "List1", "List2"
   End Sub
   
   Private Sub cmdMoveToList1_Click()
       MoveSingleItem "List2", "List1"
   End Sub
   
   Private Sub cmdMoveToList2_Click()
        MoveSingleItem "List1", "List2"
   End Sub
   
   Sub MoveSingleItem(strSourceControl As String, strTargetControl As String)
       
       Dim strItem As String
       Dim intColumnCount As Integer
       For intColumnCount = 0 To Me.Controls(strSourceControl).ColumnCount - 1
           strItem = strItem & Me.Controls(strSourceControl).Column(intColumnCount) & ";"
       Next
       strItem = Left(strItem, Len(strItem) - 1)
   
       'Check the length to make sure something is selected
       If Len(strItem) > 0 Then
           Me.Controls(strTargetControl).AddItem strItem
           Me.Controls(strSourceControl).RemoveItem Me.Controls(strSourceControl).ListIndex
       Else
           MsgBox "Please select an item to move."
       End If
   
   End Sub
   
   
   Sub MoveAllItems(strSourceControl As String, strTargetControl As String)
       Dim strItem As String
       Dim intColumnCount As Integer
       Dim lngRowCount As Long
       
       For lngRowCount = 0 To Me.Controls(strSourceControl).ListCount - 1
           For intColumnCount = 0 To Me.Controls(strSourceControl).ColumnCount - 1
               strItem = strItem & Me.Controls(strSourceControl).Column(intColumnCount, lngRowCount) & ";"
               
           Next
           strItem = Left(strItem, Len(strItem) - 1)
           Me.Controls(strTargetControl).AddItem strItem
           strItem = ""
       Next
           
       Me.Controls(strSourceControl).RowSource = ""
   End Sub
   					

7. On the Tools menu, click References and ensure that the Microsoft DAO 3.6 Object Library or the Microsoft ActiveX Data Objects 2.x Library (where 2.x refers to version 2.1 or later) is selected, depending on whether you are using Northwind.mdb or NorthwindCS.adp, and then close the Visual Basic Editor.
8. In Design view of the form, set the OnClick property for each of the command buttons to [Event Procedure].
9. Save the form, and then open it in Form view.
10. Select one or all items in either list, and then use the command buttons to move the item or items from one list to the other.