Microsoft KB Archive/278378

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

Article ID: 278378

Article Last Modified on 1/29/2007

-

APPLIES TO


 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q278378



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



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.



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:  Start Access, and then open the sample database Northwind.mdb or the sample project NorthwindCS.adp. Create a new form in Design view, and then save it with the name Select Customers. 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.  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         <<  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 = &quot;SELECT CustomerID, CompanyName FROM Customers&quot; Set db = CurrentDb Set rs = db.OpenRecordset(strSQL) Do Until rs.EOF strItem = rs.Fields(&quot;CustomerID&quot;).Value & &quot;;&quot; _ & rs.Fields(&quot;CompanyName&quot;).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 </li>  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 = &quot;SELECT CustomerID, CompanyName FROM Customers&quot; Set cn = Application.CurrentProject.Connection Set rs = New ADODB.Recordset rs.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic Do Until rs.EOF strItem = rs.Fields(&quot;CustomerID&quot;).Value & &quot;;&quot; _ & rs.Fields(&quot;CompanyName&quot;).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 </li></ul> </li>  Add the following procedures for both Northwind.mdb or NorthwindCS.adp: Private Sub cmdMoveAllToList1_Click MoveAllItems &quot;List2&quot;, &quot;List1&quot; End Sub

Private Sub cmdMoveAllToList2_Click MoveAllItems &quot;List1&quot;, &quot;List2&quot; End Sub

Private Sub cmdMoveToList1_Click MoveSingleItem &quot;List2&quot;, &quot;List1&quot; End Sub

Private Sub cmdMoveToList2_Click MoveSingleItem &quot;List1&quot;, &quot;List2&quot; 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) & &quot;;&quot; 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 &quot;Please select an item to move.&quot; 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) & &quot;;&quot; Next strItem = Left(strItem, Len(strItem) - 1) Me.Controls(strTargetControl).AddItem strItem strItem = &quot;&quot; Next Me.Controls(strSourceControl).RowSource = &quot;&quot; End Sub </li> 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.</li> In Design view of the form, set the OnClick property for each of the command buttons to [Event Procedure].</li> Save the form, and then open it in Form view.</li> 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.</li></ol>

Additional query words: OfficeKBHowTo inf listbox combobox combo box ACC2002 ACC2003

Keywords: kbprogramming kbhowto KB278378

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.