Microsoft KB Archive/274809

= ACC2000: How to Move Items from One List Box to Another List Box on a Data Access Page =

Article ID: 274809

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q274809



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
This article describes how to create two list boxes on a data access page and how to program the list boxes and command buttons so that you can move items from one list box to the other. This behavior is like the multiple-selection capability that Microsoft Access wizards use.



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. The method described in this article uses a Yes/No field in the table to indicate which records are selected. One list box displays the Yes records, and the other list box displays the No records. For moving 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 queried again to update their respective lists.

You can double-click an item in a list box to move it to the other list box. In addition to the two list boxes, the data access page also has two command buttons. You can use the command buttons to move all of the items from one list box to the other list box.

To create this example in the Northwind.mdb sample database, follow these steps:  Open the Northwind.mdb sample database. Create two queries that are based on the Products table:  In the Database window, click Queries, and then click New. In the New Query dialog box, click Design View, and then click OK. In the Show Table dialog box, on the Tables tab, click Products, click Add, and then click Close. Add the following three fields to the query design grid:

'''ProductID

ProductName

Discontinued'''

</li> In the Criteria cell for the Discontinued column, type 0 .</li> On the File menu, click Save, and then save the query as qryCurrent.</li> Close the query.</li> Repeat the preceding steps to create the second query. Include the same fields in the design grid. However, in the Criteria cell for the Discontinued column, type -1, and then save the query as qryDiscontinued.</li></ol> </li> Create a new, blank data access page, and then save the page as dapMoveItems.htm: <ol style="list-style-type: lower-alpha;"> In the Database window, click Pages, and then click New.</li> In the New Data Access Page dialog box, click Design View, and then click OK.</li>  Click the place that says &quot;Click here and type title text,&quot; and then type the following instructions: <pre class="fixed_text">  Double-click an item to move that item from one list box to the other list box. Click >> to move all items from the Current list box to the Discontinued list box. Click << to move all items from the Discontinued list box to the Current list box. You can change the font size for the text by selecting the text and then clicking a different size in the Font Size box on the toolbar. </li> On the File menu, click Save. Under Save in list, click the folder where you want to save the page. In the File name box, type dapMoveItems.htm, and then click Save.</li></ol> </li> Add the list boxes and command buttons to the Unbound section of the dapMoveItems page: <ol style="list-style-type: lower-alpha;">  Add the first list box to the Unbound section of the page, right-click the list box, click Properties, and then assign the following properties to the list box: <pre class="fixed_text"> ID:               lstCurrent ListBoundField:  ProductID ListDisplayField: ProductName ListRowSource:   qryCurrent Left:            1.5in Top:             0.5in NOTE: If the List Box Wizard is displayed, click Cancel to close the wizard.

</li>  Right-click the label to the left of the list box, click Properties, and then assign the following properties to the label: <pre class="fixed_text">  FontWeight: Bold InnerText: Current Left:      0.5in TextAlign: Right Top:       0.5in Width:     0.75in </li>  Add the second list box to the page, and then assign the following properties to the list box: <pre class="fixed_text">  ID:               lstDiscontinued ListBoundField:  ProductID ListDisplayField: ProductName ListRowSource:   qryDiscontinued Left:            4.5in Top:             0.5in </li>  Right-click the label to the left of the list box, and then assign the following properties to the label: <pre class="fixed_text">  FontWeight: Bold InnerText: Discontinued Left:      3.5in TextAlign: Right Top:       0.5in Width:     0.75in </li>  Under the Current list box, add a command button, and then assign the following properties to the command button: <pre class="fixed_text">  ID:        cmdMakeAllDiscontinued InnerText: >> </li>  Under the Discontinued list box, add a command button, and then assign the following properties to the command button: <pre class="fixed_text">  ID:        cmdMakeAllCurrent InnerText: << </li></ol> </li> <li>Add the script to the list boxes and command buttons: <ol style="list-style-type: lower-alpha;"> <li>On the Tools menu, point to Macro, and then click Microsoft Script Editor.</li> <li>If the Script Outline window is not already displayed, point to Other Windows on the View menu, and then click Script Outline.</li> <li>Expand Client Objects & Events.</li> <li> Expand the cmdMakeAllCurrent node, and then double-click the onclick node to add the following script to the HTML window: <SCRIPT LANGUAGE=vbscript FOR=cmdMakeAllCurrent EVENT=onclick> </SCRIPT> Type the following script between the script tags: Dim Con Dim Rst 'Instantiate the ActiveX Data Objects. Set Con = MSODSC.Connection Set Rst = CreateObject(&quot;ADODB.Recordset&quot;) 'Open the recordset as: '      adOpenDynamic = 2 '   adLockOptimistic = 3 Rst.Open &quot;Products&quot;, Con, 2, 3 Rst.MoveFirst 'Set Discontinued field to True. Do until Rst.EOF Rst.Fields(&quot;Discontinued&quot;).Value = 0 Rst.Update Rst.MoveNext Loop

'Destroy ActiveX Data Objects. Set Rst = Nothing Set Con = Nothing 'Refresh the list boxes. MSODSC.RecordSetDefs.Item(&quot;qryCurrent&quot;).ServerFilter = &quot;[Discontinued]=0&quot; MSODSC.RecordSetDefs.Item(&quot;qryDiscontinued&quot;).ServerFilter = &quot;[Discontinued]=-1&quot; </li> <li> Expand the cmdMakeAllDiscontinued node, double-click the onclick node, and then type the following script: Dim Con Dim Rst 'Instantiate the ActiveX Data Objects. Set Con = MSODSC.Connection Set Rst = CreateObject(&quot;ADODB.Recordset&quot;) 'Open the recordset as: '      adOpenDynamic = 2 '   adLockOptimistic = 3 Rst.Open &quot;Products&quot;, Con, 2, 3 Rst.MoveFirst 'Set Discontinued field to True. Do until Rst.EOF Rst.Fields(&quot;Discontinued&quot;).Value = -1 Rst.Update Rst.MoveNext Loop

'Destroy ActiveX Data Objects. Set Rst = Nothing Set Con = Nothing 'Refresh the list boxes. MSODSC.RecordSetDefs.Item(&quot;qryCurrent&quot;).ServerFilter = &quot;[Discontinued]=0&quot; MSODSC.RecordSetDefs.Item(&quot;qryDiscontinued&quot;).ServerFilter = &quot;[Discontinued]=-1&quot; </li> <li> Expand the lstCurrent node, double-click the ondblclick node, and then type the following script: Dim Con Dim Rst 'Instantiate the ActiveX Data Objects. Set Con = MSODSC.Connection Set Rst = CreateObject(&quot;ADODB.Recordset&quot;) 'Open the recordset as: '      adOpenDynamic = 2 '   adLockOptimistic = 3 Rst.Open &quot;Products&quot;, Con, 2, 3 Rst.MoveFirst 'Find the record for the selected item. Rst.Find &quot;ProductID=&quot; & lstCurrent.value Rst.Fields(&quot;Discontinued&quot;).Value = -1 Rst.Update 'Destroy ActiveX Data Objects. Set Rst = Nothing Set Con = Nothing

'Refresh the list boxes. MSODSC.RecordSetDefs.Item(&quot;qryCurrent&quot;).ServerFilter = &quot;[Discontinued]=0&quot; MSODSC.RecordSetDefs.Item(&quot;qryDiscontinued&quot;).ServerFilter = &quot;[Discontinued]=-1&quot; </li> <li> Expand the lstDiscontinued node, double-click the ondblclick node, and then type the following script: Dim Con Dim Rst 'Instantiate the ActiveX Data Objects. Set Con = MSODSC.Connection Set Rst = CreateObject(&quot;ADODB.Recordset&quot;) 'Open the recordset as: '      adOpenDynamic = 2 '   adLockOptimistic = 3 Rst.Open &quot;Products&quot;, Con, 2, 3 Rst.MoveFirst 'Find the record for the selected item. Rst.Find &quot;ProductID=&quot; & lstDiscontinued.value Rst.Fields(&quot;Discontinued&quot;).Value = 0 Rst.Update 'Destroy ActiveX Data Objects. Set Rst = Nothing Set Con = Nothing

'Refresh the list boxes. MSODSC.RecordSetDefs.Item(&quot;qryCurrent&quot;).ServerFilter = &quot;[Discontinued]=0&quot; MSODSC.RecordSetDefs.Item(&quot;qryDiscontinued&quot;).ServerFilter = &quot;[Discontinued]=-1&quot; </li> <li>Quit Microsoft Script Editor. Click Yes to save changes.</li></ol> </li> <li>In Access, open the data access page, and then click Page View on the View menu. Follow the instructions that are displayed at the top of the data access page to move items from one list box to another.</li></ol>

Keywords: kbhowto kbdap kbdapscript KB274809

-

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

© Microsoft Corporation. All rights reserved.