Insert a datasheet into a form

A datasheet is a simple view of data arranged in rows and columns. If you double-click a table in the Navigation Pane, Microsoft Office Access 2007 displays the table as a datasheet. Due to their compact presentation of data, datasheets work well when they are used as subforms to display data from the "many" side of a one-to-many relationship. This article describes how you can add datasheets to your forms in Office Access 2007.

What do you want to do?

View an example of a datasheet on a form

Establish relationships between tables

Create a form that contains a datasheet by using the Form tool

Create a form that contains a datasheet by using Layout view

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.

Form with subform

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.

Top of Page

Establish relationships between tables

Before you design a form that contains a datasheet, you should review the relationships between your tables and add any relationships that are missing. Doing this enables Access to automatically create the links between datasheets and main forms. To view, add, or modify relationships between the tables in your database, on the Advanced Tools tab, in the Analyze group, click Relationships. For more information about creating relationships, see the links in the See Also section.

Top of Page

Create a form that contains a datasheet by using the Form tool

When you use the Form tool to build a form that is based on a table that is on the "one" side of a single one-to-many relationship, Access automatically inserts a datasheet that displays the data on the "many" side of the relationship.

For example, if the following conditions are true, Access inserts a datasheet that shows all the orders for each customer:

You create a simple form that is based on the Customers table.
You have defined a one-to-many relationship with the Orders table that specifies that one record in the Customers table can be related to many records in the Orders table.
The Customers table is not on the "one" side of any other one-to-many relationships.
In the Navigation Pane, click the table that is on the "one" side of the one-to-many relationship.
On the Create tab, in the Forms group, click Form.

Access creates the form with the datasheet at the bottom of the form. If Access does not create a datasheet, it means either that the table you used for the form is not part of a one-to-many relationship, or that the table is part of more than one one-to-many relationship.

Notes:

Aside from creating the main form, this process does not create any other form objects. The datasheet is a direct view of the table.
To hide a column in a datasheet Right-click the column and then click Hide Column. This setting is saved; if you save and close the form and then reopen it, the column will still be hidden.
To restore a hidden column in a datasheet Right-click any column heading in the datasheet and then click Unhide Columns. Select the check box next to each column that you want to restore to the view, and then click Close.

Top of Page

Create a form that contains a datasheet by using Layout view

If you have already defined the relationships between the tables in your database, you can quickly create a form that contains a datasheet 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.

On the Create tab, in the Forms group, click Blank Form.

Access opens a blank form in Layout view and displays the Field List pane.

Note: If you accidentally close the Field List pane, you can reopen it by doing one of the following:
- Press ALT+F8.
  
  —or—
  
  On the Format tab, in the Controls group, click Add Existing Fields.
Click the plus sign next to the table that is on the "one" side of the one-to-many relationship, and then drag the first field that you want onto the form.

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.
From the Fields available for this view section, drag any other fields that you want onto the main form.
From the Fields available in related tables section, drag the first field that you want in your datasheet onto the form. Access creates a datasheet and adds the field to it.
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.
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 or datasheet 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 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 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.

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 Format tab, in the Controls 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.
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.
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.
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.

Open the form in Design view by right-clicking the form in the Navigation Pane, and then clicking Design View.
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.
On the Design tab, in the Controls group, click the Subform/Subreport button.
In the form design grid, click where you want the datasheet to be inserted.
Reposition and resize the subform control by dragging the handles on the edges and corners of the control.
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.
If the property sheet is not already displayed, press F4 to display it.
If the subform control is not currently selected, click it once to select it.
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.
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.
Save the form by clicking Save on the Quick Access Toolbar or by pressing CTRL+S.
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

Microsoft Office Tutorials

Saturday, March 31, 2018