Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

Summary

This article shows you how to manage two lists so that items from one list can be transferred singly or as a group to the second list and vice versa. The process is similar to that seen in Access wizards such as the List Box wizard itself.

↑ Back to the top


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.

↑ Back to the top


Keywords: KB278378, kbhowto, kbprogramming

↑ Back to the top

Article Info
Article ID : 278378
Revision : 9
Created on : 1/29/2007
Published on : 1/29/2007
Exists online : False
Views : 484