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. ACC2000: How to Move Items from One List Box to Another List Box on a Data Access Page

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

View products that this article applies to.

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).

↑ Back to the top

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.

↑ 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. 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.

↑ Back to the top

Keywords: KB274809, kbdapscript, kbdap, kbhowto

↑ Back to the top

Article Info
Article ID : 274809
Revision : 3
Created on : 6/23/2005
Published on : 6/23/2005
Exists online : False
Views : 266