Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

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

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:

1. Create a new page and find the query you want in the field list.

2. Add fields from the query to the page, but leave off the key field,
   ProductID.

3. Add the ProductID field to the page.

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

1. Return to design view.

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

3. Select Products from the drop-down list, as shown in Figure 13-9.

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

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