Insert a datasheet into a form

What do you want to do?

• View an example of a datasheet on a form

• Drag and drop a datasheet into a form

• Insert a datasheet into an existing form by using Layout view

• Insert a datasheet into an existing form by using Design view

• Understand the differences between a split form and a form that contains a datasheet

View an example of a datasheet on a form

Suppose you want to create a form that shows data about product categories and also all the products within those categories. The data in the Categories table represents the "one" side of the relationship, and the data in the Products table represents the "many" side of the relationship. Each category can have many products.

1. The main form shows data from the "one" side of the relationship.

2. The datasheet shows data from the "many" side of the relationship.

Drag and drop a datasheet into a form

The quickest way to add an existing datasheet into a form is open the form in Design view and then click, hold, and drag an existing datasheet form from the Navigation Pane onto the form design area.

If you set up the relationships correctly before you started this procedure, Access automatically sets the Link Child Fields and the Link Master Fields properties with the correct values that will link the main form to the datasheet.

My Link Child Fields and Link Master Fields properties are blank

If Access cannot determine how to link the subform control to the main form, it leaves the Link Child Fields and Link Master Fields properties of the subform control blank. You must set these properties manually.

Insert a datasheet into an existing form by using Layout view

If you have already defined the relationships between the tables in your database, you can quickly add a datasheet to an existing form by using Layout view. When you add a field from the "many" side of a relationship to a form that is based on the "one" side of the relationship, Access automatically creates a datasheet to display the related records.

1. Open the existing form in Layout view by right-clicking it in the Navigation pane, and then clicking Layout View on the shortcut menu.

   If the Field List pane is not displayed:

   • Press ALT+F8.

     —or—

     On the Design tab, in the Tools group, click Add Existing Fields.

     Note that the Field List pane divides the tables into sections: Fields available for this view and, below that, Fields available in related tables. At the bottom of the Field List pane, any other tables that are not directly related to the main table are listed under Fields available in other tables.

2. In the Fields available in related tables section, expand the table that contains the first field that you want on your datasheet, and drag the field onto the form. Access creates a datasheet and adds the field to it.

3. To add additional fields to the new datasheet, you must first click the datasheet to select it. Note that Access rearranges the Field List pane so that the fields available for the datasheet are at the top. From the Fields available for this view section, drag any other fields that you want onto the datasheet. As you drag each field into the datasheet, Access draws an insertion bar to show you where the field will be inserted when you release the mouse button.

4. Continue adding fields to the main form or the datasheet by first clicking the main form or datasheet to select it, and then dragging the field from the Field List pane.

The Fields available in other tables section of the Field List pane contains tables that are not directly related to the table or query that the selected form is based on. If you have defined all the valid table relationships in the Relationships window, then the fields in the Fields available in other tables section are not likely to be valid selections for the form that you are building. If you drag a field from this section to the form, Access displays the Specify Relationship dialog box. You must enter the field or fields that relate the two tables to each other before you proceed. If you do this, the Field List pane will no longer be sorted into sections according to the predefined relationships. It might be best to click Cancel in this dialog box and reexamine your table relationships instead. For more information about relationships, see the article Create, edit or delete a relationship.

Top of Page

Insert a datasheet into an existing form by using Design view

Use this procedure to add a datasheet that is based directly on a table or query to an existing form in Design view.

1. Open the form in Design view by right-clicking the form in the Navigation Pane, and then clicking Design View.

2. On the Design tab, in the Controls group, if the Use Control Wizards tool is selected, click it so that it is not selected.

   

   Why?

   If used, the subform Control Wizard builds a separate form object and bases the subform control on that form object. By contrast, this procedure creates a datasheet that is based directly on a table or query rather than a form.

3. On the Design tab, in the Controls group, click the Subform/Subreport button.

4. In the form design grid, click where you want the datasheet to be inserted.

5. Reposition and resize the subform control by dragging the handles on the edges and corners of the control.

6. Edit the attached label for the subform control by first clicking the label, double-clicking it to select its text, and then typing a new label. If you prefer to delete the label, click the label once and then press DELETE.

7. If the property sheet is not already displayed, press F4 to display it.

8. If the subform control is not currently selected, click it once to select it.

9. On the Data tab of the property sheet, click the Source Object drop-down list, and then click the table or query that you want to display in the datasheet. For example, if you want to display data from the Orders table, click Table.Orders.

10. If you set up the relationships correctly before you started this procedure, Access automatically sets the Link Child Fields and the Link Master Fields properties with the correct values that will link the main form to the datasheet.

    My Link Child Fields and Link Master Fields properties are blank

    If Access cannot determine how to link the subform control to the main form, it leaves the Link Child Fields and Link Master Fields properties of the subform control blank. You must set these properties manually by doing the following:

    1. Open the main form in Design view.

    2. Click the subform control once to select it.

    3. If the property sheet is not currently displayed, press F4.

    4. On the Data tab of the property sheet, click (...) next to the Link Child Fields property box.

       The Subform Field Linker dialog box appears.

    5. In the Master Fields and Child Fields drop-down lists, select the fields that you want to link the forms with. If you are not sure which fields to use, click Suggest to have Access determine the linking fields. When you finish, click OK.

       If you do not see the field that you want to use to link the forms, you might need to edit the record source of the master or child form to be sure the field is in it. For example, if the form is based on a query, be sure the linking field is present in the query results.

    6. Save the main form and switch to Form view to verify that the form works as you expect.

11. Save the form by clicking Save on the Quick Access Toolbar or by pressing CTRL+S.

12. Switch to Form view and verify that the datasheet works as you expect.

Top of Page

Understand the differences between a split form and a form that contains a datasheet

A form that contains a datasheet displays data from different (but usually related) data sources. For example, a form might show employees, and it might have a datasheet that shows all the tasks assigned to each employee. The datasheet has a different data source from the form that contains it. By contrast, a split form has two sections (a form and a datasheet), but the same data is displayed in both sections. The two sections track with each other and give you two different views of your data at once.

For more information about creating and working with a split form, see the article Create a split form.

Top of Page