Adding Subdatasheets to a Table or Query

Subdatasheets are closely related to lookup fields, but serve a different purpose. Subdatasheets display related table values in an embedded datasheet, whereas lookup fields display base table values in a combo box or list box. Both of these Access features depend on the equivalent of one-to-many queries; the difference between the queries is that the many side of a subdatasheet is a related table, whereas a lookup field uses a query against a related table to supply the many side values.

You also can cascade subdatasheets to display related data of multiple joined tables, a feature not applicable to lookup fields, but a table or query can't have more than one subdatasheet. Figure 11.31 illustrates the Customers table displaying the Orders subdatasheet for Alfreds Futterkiste with embedded sub-subdatasheets that display Order Details records.

Figure 11.31. The Customers table has a two-level subdatasheet hierarchy. Note the + and - column at the left of both the Customers and Orders subdatasheets.

For more information on subdatasheets, see "Table Properties for Subdatasheets," p. 157.

Caution

Editing data in subdatasheets can lead to serious data-entry errors. For example, if you use the Order Details subdatasheet to change an entry in the Product field, the UnitPrice value doesn't change to correspond to the price for the new product. Subdatasheets are an interesting feature, but should only be used to view, not edit, vital business data.

Table Subdatasheets

Some of the tables of Northwind.mdb already have subdatasheets; Employees doesn't. To add an HRActions subdatasheet to the Employees table, follow these steps:

1. Verify in the Relationships window that a relationship exists between the EmployeeID fields of the HRActions and Employees tables.

2. Open the Employees table in Datasheet view.

3. Click one of the + symbols in the first column of the Employees datasheet to open the Insert Subdatasheet dialog.

   Tip

   If the + symbols aren't present, choose Insert, Subdatasheet to open the Insert Subdatasheet dialog.

4. Select the HRActions table in the list. The EmployeeID foreign-key field of the HRActions table appears in the Link Child Fields drop-down list and the EmployeeID field of the Employees table appears in the Link Master Fields list (see Figure 11.32). The The HRAction table is included in the Relationships window; the relationship supplies the default values for the two drop-down lists.

   Figure 11.32. Clicking the + symbol in a row of a table that doesn't have a subdatasheet opens the Insert Subdatasheet dialog.

   

   Note

   The Link Master Fields and Link Child Fields values create a one-to-many join on the specified fields.

5. Click OK to add the subdatasheet and close the dialog. The subdatasheet for the selected record opens automatically.

6. Click one or two of the + symbols in the Employees datasheet to display the newly added subdatasheets (see Figure 11.33).

   Figure 11.33. Only one HRActions record exists for each employee at this point.

   

7. Change to Table Design view and click the Properties button to display the Table Properties window. The selections you make in the Insert Subdatasheet dialog appear in the subdatasheet-related properties of the table (see Figure 11.34).

   Figure 11.34. You also can add a subdatasheet by opening the Table Properties window and selecting a table or query from the Subdatasheet name list.

   

Note

The child (foreign-key) field, EmployeeID, doesn't appear as a column of the subdatasheet. When you add a new record in the subdatasheet, Access automatically inserts the primary-key value of the selected base-table record into the related record. In this case, Access adds the EmployeeID value from the Employees field to the EmployeeID value of the HRActions table.

The default value of the Subdatasheet Name property for new tables you create is [Auto], which adds the column of boxed + symbols to a new table datasheet. To open the Add Subdatasheet dialog for a new table, choose Insert, Subdatasheet. Alternatively, you can set the subdatasheet properties directly in the Table Properties window. To remove a subdatasheet, set the Subdatasheet Name property value to [None]. If you remove a subdatasheet from a table, setting Subdatasheet Name to [Auto] displays the boxed + symbols and lets you add a new subdatasheet in Datasheet view.

Query Subdatasheets

If you don't want your subdatasheet to display all the related table's columns, you must design a simple select query with only the desired fields and then use the query to populate the subdatasheet. As an example, you can minimize the width of the Orders subdatasheet of the Customers table by doing the following:

1. In Design view, create a simple SELECT query that includes only the OrderID, CustomerID (required for the master-child join), OrderDate, ShippedDate, and ShippedVia fields of the Orders table.

2. Double-click the top pane to open the Query Properties window, and set the Recordset Type property to Snapshot. Selecting Snapshot creates a read-only subdatasheet to prevent editing. Close the query and save it as qryShortOrders.

3. Open the Customers table in Design view, open the Table Properties window, and select Query.qryShortOrders from the Subdatasheet Name list. CustomerID remains the value of the linked fields.

4. Return to Datasheet view, saving your changes. The expanded subdatasheet appears as shown in Figure 11.35, without the + sign column. The query is read-only, so the subdatasheet has no tentative append record and you can't edit the data.

   Figure 11.35. Use a Snapshot query to create a read-only subdatasheet.

   

5. In Design view, create another select query that includes all fields (*) of the Order Details table. Open the Query Properties window, set the Recordset Type property value to Snapshot, close the windows, and save the query as qryShortOrderDetails.

6. Close the Customers table, open qryShortOrders in Design view, right-click an empty area of the upper pane, and choose Properties to open the Query Properties window.

7. Select Query.qryOrderDetails in the Subdatasheet Name field and then type OrderID in the two Link...Fields text boxes (see Figure 11.36). You must type the field names because you haven't established a relationship between the query and table in the Relationships window.

   Figure 11.36. After adding Query.qryOrderDetails as the value of the Subdatasheet Name property, you manually set the Link Child Fields and Link Master Fields property values.

   

8. Run the query and then expand one or more of the subdatasheets to test your work (see Figure 11.37).

   Figure 11.37. The read-only qryShortOrders query has a read-only query subdatasheet based on qryOrderDetails.

   

9. Close qryShortOrders, save your changes, open the Customers table, and display the subdatasheets. The new version of the Customers table appears as shown in Figure 11.38. You can open the Products list, but you can't change the value of the Product column.

   Figure 11.38. The subdatasheet and sub-subdatasheet are read-only, but the Customers table continues to have read-write attributes, as indicated by its tentative append record.