SubdatasheetsA Product of Relationships

Did you notice the plus signs (+) to the left of each record in the Catalog table as you worked with it? Access displays those signs in the primary key table when a relationship exists between two tables. If you open all three tables, as we've done in Figure 6.17, you'll see that the Types and Catalog tables both have a column of plus signs, but the Plants table doesn't.

Figure 6.17. The plus signs in a table indicate that Access has related records available.


The plus sign means that the corresponding record has at least one related record in another table. If a relationship exists between two tables but no related record exists for a particular primary key value, Access displays a minus sign () instead of the plus sign.

Click the plus sign to the left of Gurney's in the Catalog table. The result is shown in Figure 6.18. Specifically, a subdatasheet displays the records for Cosmos and German Chamomile. A subdatasheet displays related values via an embedded datasheet (table).

Figure 6.18. Click a record's plus sign to display related records.


You can disable this feature if you like. To do so, follow these steps:

  1. Open the table in Design view (click the View button on the Table Datasheet toolbar).

  2. Click the Properties button on the Table Design toolbar.

  3. In the resulting dialog box, select [None] from the Subdatasheet Name property's drop-down list. The default option is [Auto], which automatically displays one-to-many related records.

If you know the table has related records, but the plus signs aren't visible, you can update the table's properties to display subdatasheets. For instance, the Plants table doesn't display subdatsheets by default because the primary key values have no related records. All the relationships are between that table's foreign key values and the other two tables. To display subdatasheets in the plant table, do the following:

  1. Open the Plants table in Table Datasheet view.

  2. Select Subdatasheet from the Insert menu to display the Insert Subdatasheet dialog box.

  3. Select Catalogs in the Tables tab.

  4. Select Name from the Link Child Fields control.

  5. Select CatalogName from the Link Master Fields control, and click OK .

The result of this change is shown in Figure 6.19. Click any plus sign to display that record's corresponding catalog information. You don't want to display subdatasheets in the Plants table, so close the table without saving the change you just made. (Click No when Access prompts you to save your changes.)

Figure 6.19. The Plants table now displays related records in subdatasheets.


With all the extra records and fields displayed, navigating can be a bit awkward . Refer to Table 6.2 for shortcut keys for working with and navigating subdatasheets.

Table 6.2. Shortcut Keys



Ctrl+Shift+Down Arrow

Expands a record's subdatasheet

Ctrl+Shift+Up Arrow

Collapses a subdatasheet


Enters the subdatasheet from the last field of the previous record in the datasheet


Enters the subdatasheet from the first field of the following record in the datasheet


Exits the subdatasheet and moves to the first field of the next record in the datasheet


Exits the subdatasheet and moves to the last field of the previous record in the datasheet


Enters the next field in the datasheet from the last field in the subdatasheet

Absolute Beginner's Guide to MicrosoftR Access 2002
Absolute Beginner's Guide to MicrosoftR Access 2002
Year: 2002
Pages: 133 © 2008-2017.
If you may any questions please contact us: