Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

Recipe 13.4 Create a DAP that Allows You to Update Data

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

13.4.2 Solution

DAPs in Access 2000 were almost never updateable. Since Access 2002, however, 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:

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

The second way to create an updateable page is to place fields in whatever order you like, but include the key field and remember to set the UniqueTable property. To experiment with this technique, first follow these steps to create a page that isn't updateable:

Create a new page and find the query you want in the field list.
Add fields from the query to the page, but leave off the key field, ProductID.
Add the ProductID field to the page.
Switch to page view. You won't be able to edit any fields on the page.

At this point, the page is not updateable because the DataSource control doesn't support the ability to update both sides of a one-to-many query, and it needs more information to figure out which table is on the "many" side of the join. To make this page updateable, follow these steps (or open 13-04.MDB to see the completed sample):

Return to design view.
Display the properties sheet for the header section, and find the UniqueTable property (in the Data page of the properties sheet). The UniqueTable property tells the DataSource control which of the tables is on the "many" side of the relationship.
Select Products from the drop-down list, as shown in Figure 13-9.

Figure 13-9. Selecting the "many" table in a relationship

Switch to page view. You will now be able to edit fields from the Products table.

To make the page read-only again, just delete the ProductID from the page. The key field of the "many" table must be displayed on the form, although you can hide it by changing the Visibility property of the control.

Sometimes you may want to restrict the user from updating data on a page. To do so, look for the AllowAdditions, AllowDeletions, and AllowEdits properties on the Group Level properties sheet. If you want to keep the user from editing only certain controls, keep the AllowEdits property set to True, but change the ReadOnly property of the individual controls to True as necessary.

[ Team LiB ]