ACC2000: How to Add a Record to a Dropdown List on a Data Access Page

On a form, you can use the NotInList event of a combo box to add records to an underlying table. This article describes how to use the HTML dropdown list control to simulate this functionality on a data access page. This article uses the showModalDialog method of the Document Object Model (DOM) of Microsoft Internet Explorer to open a child window and the ServerFilter property of a RecordsetDef object for the data source control on the page.

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. Dropdown list controls on data access pages are implemented with the HTML SELECT tag, which does not support entering new values as do combo boxes on Access forms. You can, however, use a second page to add a value to the record source for the dropdown list. This example consists of a products page that has a button to add a new category on a second page.

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.

Creating the First Page

1. Open the sample database Northwind.mdb or the sample Access project NorthwindCS.adp.
2. In the Database window, click Pages under Objects, and then click New.
3. In the New Data Access Page box, click AutoPage: Columnar, click Products in the Choose the table or query/view where the object's data comes from box, and then click OK.
4. On the View menu, click Design View.
5. Add the following controls to the page, and then set the properties of the controls as indicated:

      Command Button 
      -------------------
      Id: cmdNewCategory
      Value: New Category
        
      Dropdown List 
      ------------------------------
      Id: cboCategory
      ControlSource: CategoryID
      ListRowSource: Categories
      ListBoundField: CategoryID
      ListDisplayField: CategoryName
   					

6. On the Tools menu, point to Macro, and then click Microsoft Script Editor.
7. On the View menu, point to Other Windows, and then click Script Outline. This places the focus on the Client Objects & Events node.
8. Expand Client Objects & Events.
9. Expand the cmdNewCategory object, and then double-click the onclick event.
10. Type the following code between the SCRIPT tags:

    'Get the return value from the showModalDialog method.
    'Similar to opening a modal form in Access, the remaining
    'code will not execute until the modal dialog window closes
    window.showModalDialog "AddCategory.htm", "", "dialogHeight:20,dialogWidth:25"
    		
    'Refresh the Categories RecordsetDef in cboCategories.
    'Setting the ServerFilter property will requery
    'the underlying record source
    MSODSC.Recordsetdefs("Categories").ServerFilter = ""
    					

11. Save and then close this page.

Creating the Second Page

1. Create a new AutoPage: Columnar data access page named AddCategory.htm, and set the following page properties:

      Title: Add Category
      RecordSource: Categories
      DataEntry: True
   					

2. Save and then close the page.

View the Products page in Internet Explorer 5 or later. To test these pages, click the New Category button. Enter values in the AddCategory page, and click the Save button on the Navigation bar. Close this page using the Close button (the X at the top of the window). Note that the AddCategory page closes and that the new category that is added appears in the dropdown list on the Products page.

For additional information about using the NotInList event in an Access form, click the article number below to view the article in the Microsoft Knowledge Base: For additional information about how to create data entry data access pages, click the article number below to view the article in the Microsoft Knowledge Base: