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

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:

1. Open the Northwind.mdb sample database.
2. Create two queries that are based on the Products table:
   1. In the Database window, click Queries, and then click New.
   2. In the New Query dialog box, click Design View, and then click OK.
   3. In the Show Table dialog box, on the Tables tab, click Products, click Add, and then click Close.
   4. Add the following three fields to the query design grid:

      ProductID
      
      ProductName
      
      Discontinued
      

   5. In the Criteria cell for the Discontinued column, type 0.
   6. On the File menu, click Save, and then save the query as qryCurrent.
   7. Close the query.
   8. 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.
3. Create a new, blank data access page, and then save the page as dapMoveItems.htm:
   1. In the Database window, click Pages, and then click New.
   2. In the New Data Access Page dialog box, click Design View, and then click OK.
   3. Click the place that says "Click here and type title text," and then type the following instructions:

         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.
   4. 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.
4. Add the list boxes and command buttons to the Unbound section of the dapMoveItems page:
   1. 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:

        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.
      
      
   2. Right-click the label to the left of the list box, click Properties, and then assign the following properties to the label:

         FontWeight: Bold
         InnerText:  Current
         Left:       0.5in
         TextAlign:  Right
         Top:        0.5in
         Width:      0.75in
      						

   3. Add the second list box to the page, and then assign the following properties to the list box:

         ID:               lstDiscontinued
         ListBoundField:   ProductID
         ListDisplayField: ProductName
         ListRowSource:    qryDiscontinued
         Left:             4.5in
         Top:              0.5in

   4. Right-click the label to the left of the list box, and then assign the following properties to the label:

         FontWeight: Bold
         InnerText:  Discontinued
         Left:       3.5in
         TextAlign:  Right
         Top:        0.5in
         Width:      0.75in
      						

   5. Under the Current list box, add a command button, and then assign the following properties to the command button:

         ID:        cmdMakeAllDiscontinued
         InnerText: >>
      						

   6. Under the Discontinued list box, add a command button, and then assign the following properties to the command button:

         ID:        cmdMakeAllCurrent
         InnerText: <<
      						

5. Add the script to the list boxes and command buttons:
   1. On the Tools menu, point to Macro, and then click Microsoft Script Editor.
   2. If the Script Outline window is not already displayed, point to Other Windows on the View menu, and then click Script Outline.
   3. Expand Client Objects & Events.
   4. 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("ADODB.Recordset")
           
        'Open the recordset as:
        '       adOpenDynamic = 2
        '    adLockOptimistic = 3
        Rst.Open "Products", Con, 2, 3
        Rst.MoveFirst
            
        'Set Discontinued field to True.
        Do until Rst.EOF
            Rst.Fields("Discontinued").Value = 0
            Rst.Update
            Rst.MoveNext
        Loop
      
        'Destroy ActiveX Data Objects.
        Set Rst = Nothing
        Set Con = Nothing
        
        'Refresh the list boxes.
        MSODSC.RecordSetDefs.Item("qryCurrent").ServerFilter = "[Discontinued]=0"
        MSODSC.RecordSetDefs.Item("qryDiscontinued").ServerFilter = "[Discontinued]=-1"  
      						

   5. 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("ADODB.Recordset")
           
        'Open the recordset as:
        '       adOpenDynamic = 2
        '    adLockOptimistic = 3
        Rst.Open "Products", Con, 2, 3
        Rst.MoveFirst
            
        'Set Discontinued field to True.
        Do until Rst.EOF
            Rst.Fields("Discontinued").Value = -1
            Rst.Update
            Rst.MoveNext
        Loop
      
        'Destroy ActiveX Data Objects.
        Set Rst = Nothing
        Set Con = Nothing
        
        'Refresh the list boxes.
        MSODSC.RecordSetDefs.Item("qryCurrent").ServerFilter = "[Discontinued]=0"
        MSODSC.RecordSetDefs.Item("qryDiscontinued").ServerFilter = "[Discontinued]=-1"  
      						

   6. 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("ADODB.Recordset")
           
        'Open the recordset as:
        '       adOpenDynamic = 2
        '    adLockOptimistic = 3
        Rst.Open "Products", Con, 2, 3
        Rst.MoveFirst
            
        'Find the record for the selected item.
        Rst.Find "ProductID=" & lstCurrent.value
        Rst.Fields("Discontinued").Value = -1
        Rst.Update
       
        'Destroy ActiveX Data Objects.      
        Set Rst = Nothing
        Set Con = Nothing
      
        'Refresh the list boxes.
        MSODSC.RecordSetDefs.Item("qryCurrent").ServerFilter = "[Discontinued]=0"
        MSODSC.RecordSetDefs.Item("qryDiscontinued").ServerFilter = "[Discontinued]=-1"  
      						

   7. 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("ADODB.Recordset")
           
        'Open the recordset as:
        '       adOpenDynamic = 2
        '    adLockOptimistic = 3
        Rst.Open "Products", Con, 2, 3
        Rst.MoveFirst
            
        'Find the record for the selected item.
        Rst.Find "ProductID=" & lstDiscontinued.value
        Rst.Fields("Discontinued").Value = 0
        Rst.Update
       
        'Destroy ActiveX Data Objects.      
        Set Rst = Nothing
        Set Con = Nothing
      
        'Refresh the list boxes.
        MSODSC.RecordSetDefs.Item("qryCurrent").ServerFilter = "[Discontinued]=0"
        MSODSC.RecordSetDefs.Item("qryDiscontinued").ServerFilter = "[Discontinued]=-1"  
      						

   8. Quit Microsoft Script Editor. Click Yes to save changes.
6. 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.