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


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q268012
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SUMMARY

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.

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

REFERENCES

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:

197526 ACC2000: Use NotInList Event to Add a Record to Combo Box

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:

262453 ACC2000: How to Create a Data Access Page to Open to New Records Only

Modification Type:MajorLast Reviewed:6/23/2005
Keywords:kbDAP kbDAPScript kbhowto KB268012
©2004 Microsoft Corporation. All rights reserved.