Access Cookbook

Create a DAP That Allows You to Update Data

Problem

Most of the time, the pages you create are updateable; that is, the user can change the underlying data. Occasionally, though, the data can’t be changed, and it isn’t clear to you why not.

Solution

DAPs in Access 2000 were almost never updateable. In Access 2002, though, most of the limitations have gone away, and you can nearly always edit the underlying data. If each group on your page is based on a single table or table query, the data is always updateable. If you base a group on a multitable, one-to-many query, the table on the “many” side of the join (or “most-many,” if there are several tables) is editable only if the key field of that table is placed on the page and the UniqueTable property for the group is set to that table.

There are two ways to create an updateable page based on a multitable query. The careful way requires that the key field of the table you want to update be the first field placed in the group. Follow these steps (exactly) to create an updateable page the careful way:

1. Create a new page and find the query you want to use in the field list window. We’ll use qryProductCategory in the sample database.

2. Add the key field of the table you want to edit to the page. In our example, the table is Products and its key field is ProductID.

3. Add whatever other fields you’d like and switch to page view. You should be able to edit fields from the Products table (except ProductID, which is an AutoNumber field).