In a subdatasheet (subdatasheet: A datasheet that is nested within another datasheet and that contains data related or joined to the first datasheet.), you can view and edit related (relationship: An association that is established between common fields (columns) in two tables. A relationship can be one-to-one, one-to-many, or many-to-many.) or joined (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) data in a table, query, or form datasheet, or in a subform (subform: A form contained within another form or a report.). For example, in the Northwind sample database, the Suppliers table has a one-to-many relationship (one-to-many relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.) with the Products table; so for each row of the Suppliers table in Datasheet view (Datasheet view: A view that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.), you can view and edit the related rows of the Products table in a subdatasheet.
Click the expand indicator to expand or collapse a subdatasheet
Microsoft Access automatically creates a subdatasheet in a table that is in a one-to-one relationship (one-to-one relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of one, and only one, record in the related table.), or is on the "one" side of a one-to-many relationship, when the SubdatasheetName property of the table is set to Auto. A relationship is defined by matching primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) and foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) fields in the related tables.
However, you can add a subdatasheet to any table, query, or form. A subdatasheet can have as its source object a table or query (specified in the SubdatasheetName property). A subdatasheet corresponding to a subform control (subform/subreport control: A control that displays a subform in a form or a subform or a subreport in a report.) can have as its source object a table, query, or form (specified in the subform control's SourceObject property).
You can nest a subdatasheet within a subdatasheet for up to eight levels. However, each datasheet or subdatasheet can have only one nested subdatasheet. For example, the Customers table can contain one Orders table subdatasheet, and the Orders table subdatasheet can contain one Orders Details subdatasheet. But the Customers table can't contain both an Orders table subdatasheet and a Salespeople query subdatasheet.
Forms, subforms, and subdatasheets
A form in Form view (Form view: A view that displays a form to show or accept data. Form view is the primary means of adding and modifying data in tables. You can also change the design of a form in this view.) or Datasheet view can also have a subdatasheet, but there are differences between a form subdatasheet and a table or query subdatasheet:
- You insert a subdatasheet into a form by adding a subform control, and then defining a source object by setting the subform control's SourceObject property to a table, query, or form. When you view the form in Datasheet view, the subform appears as a subdatasheet. If the subform's source object is a form, its DefaultView property can be set to Datasheet, Single View, or Continuous Form. Therefore, in form Datasheet view, you can also see the subdatasheet displayed as a single or continuous form.
- Although a form can have more than one subform, each of which can be nested for up to two levels, in form Datasheet view only one subdatasheet is displayed. Access displays the subdatasheet corresponding to the first subform in the tab order of the form.
- When you first open a table, query, or form in Datasheet view, its subdatasheet is not expanded; however, when you first open a form in Form view, the subdatasheet corresponding to the subform is expanded.
- You can permanently display the foreign key or matching fields in the subdatasheet corresponding to the subform if the subform's source object is a form.