Microsoft KB Archive Search

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.

  1. Home
  2. How to use AddItem and RemoveItem to move selections from one list box to another

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 : 513