ACC2000: How to Synchronize Two Drop-down Lists on a Data Access Page

This article demonstrates a technique that you can use to synchronize two drop-down lists on a data access page. You can also apply this same technique to group filter controls or a combination of both control types. Although the end result of this technique is similar to synchronizing combo boxes on an Access form, the method that is used in this technique is very different.

The following example uses the sample database Northwind.mdb. The first drop-down list contains all of the available customers and the second drop-down list contains all of the available orders for the customer selected in the first drop-down list.

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.

1. Open the sample database Northwind.mdb.
2. In the Database window, click Pages under Objects, and then click New.
3. In the New Data Access Page dialog box, click Design View, and then click OK.
4. If the toolbox is not already visible, click Toolbox on the View menu.
5. Use the Control Wizard to create a new drop-down list that is based on the Customers table. To make sure that you are using the Control Wizard, go to the toolbox and click the Control Wizards button, so that it appears to be pressed in.
6. Include the following fields in the control's source:
   • CustomerID
   • CompanyName
7. Change the ID property of the CustomerID control to ddlCustomer.
8. Create a second drop-down list on the page that is based on the Orders table.
9. Include the following field in the control's source:
   • OrderID
10. Change the ID property of the OrderID control to ddlOrder.
11. On the Tools menu, point to Macro, and then click Microsoft Script Editor.
12. In the Script Outline pane of the Script Editor, browse to the onchange event of the ddlCustomer control.
13. Type the following script for the event:

    'There are three values in this line of script that will need to be altered 
    'to port this successfully to another page.
    '
    'Orders is the table on which the secondary dropdown list is based.
    'CustomerID is the name of the field common to both dropdown lists.
    'ddlCustomer is the name of the primary dropdown list.
    '
    MSODSC.RecordsetDefs.Item("Orders").ServerFilter = "[CustomerID]='" & ddlCustomer.value & "'"
    					

14. In the Script Outline pane of the Script Editor, browse to the onload event of the window control.
15. Type the following script for the event:

    ddlCustomer.value = ""
    					

16. In the Script Outline pane of the Script Editor, browse to the Current event of the MSODSC control.
17. Type the following script for the event:

    MSODSC.RecordsetDefs.Item("SubAreas").ServerFilter = "" 
    MSODSC.RecordsetDefs.Item("SubSubAreas").ServerFilter = "" 
    					

18. Modify the script Current event script tag created in step 17 from

    <script language=vbscript for=MSODSC event=Current>

    to:

    <script language=vbscript for=MSODSC event=Current(obj)>
    					

19. Locate the Microsoft Office Data Source Control (MSODSC) in the Script Editor pane. This control is represented by an Access icon by default.
20. Place the following code immediately after the MSODSC control, including the script tags.

    <SCRIPT>
    MSODSC.RecordsetDefs.Item("Orders").ServerFilter = "1=0"
    </SCRIPT>
    					

21. On the File menu, click Save.
22. On the File menu, click Exit.
23. On the View menu, click Page View.

Test the behavior of the two drop-down lists. Note that the selection you make in the ddlCustomer control filters the values available in the ddlOrders control.