Create a form that contains a subform
When you are working with relational data (related data that is stored in separate tables), you often need to view data from multiple tables or queries on the same form. For example, you might want to see a customer record from one table and information about that customer's orders from another table at the same time. Subforms are a convenient tool for doing this, and Microsoft Access provides several ways to help you create them quickly.
In this article
Overview of subforms
A subform is a form that is inserted in another form. The primary form is called the main form. A form/subform combination is sometimes referred to as a hierarchical form, a master/detail form, or a parent/child form.
Subforms are especially effective when you want to show data from tables or queries that have a one-to-many relationship. A one-to-many relationship is 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. For example, you can create a form that displays employee data, and contains a subform that displays each employee's orders. The data in the Employees table is the "one" side of the relationship. The data in the Orders table is the "many" side of the relationship (each employee can have more than one order).
The main form shows data from the "one" side of the relationship.
The subform shows data from the "many" side of the relationship.
The main form and subform are linked so that the subform displays only records that are related to the current record in the main form. For example, when the main form displays Nancy Freehafer's information, the subform displays only her orders. If the form and subform were unlinked, the subform would display all the orders, not just Nancy's.
The following table defines some of the terminology that is associated with subforms. Access will handle most of the details if you use the procedures in this article, but it is helpful to know what is occurring behind the scenes if you need to make modifications later.
| Term | Definition |
| Subform control | The control that embeds a form into a form. You can think of the subform control as a "view" of another object in your database, whether it is another form, a table, or a query. The subform control provides properties which allow you to link the data displayed in the control to the data on the main form. |
| Source Object property | The property of the subform control that determines what object is displayed in the control. |
| Datasheet | A simple display of data in rows and columns, much like a spreadsheet. The subform control displays a datasheet when its source object is a table or query, or when its source object is a form whose Default View property is set to Datasheet. In these cases, the subform is sometimes referred to as a datasheet or subdatasheet instead of as a subform. |
| Link Child Fields property | The property of the subform control that specifies which field or fields in the subform link the subform to the main form. |
| Link Master Fields property | The property of the subform control that specifies which field or fields on the main form link the main form to the subform. |
Notes:
-
For best results, establish table relationships before following the procedures in this article. This enables Access to automatically create the links between subforms and main forms. To view, add, or modify relationships between the tables in your database, on the Database Tools tab, in the Show/Hide group, click Relationships. For more information about how to create relationships, see the links in the See Also section.
-
When a subform control has a form as its source object, it contains the fields that you place on the form, and it can be viewed as a single form, continuous form, or datasheet. One advantage of basing a subform on a form object is that you can add calculated fields to the subform, such as [Quantity] * [Unit price].
-
You can insert a datasheet or pivot view into a form by creating a subform control whose source object is a table or query. For more information, see the section Add related data to a form without creating a subform.
Create or add a subform
Use the following table to determine which procedure is most appropriate for your situation.
| Scenario | Recommended procedure |
| You want Access to create both a main form and a subform, and to link the subform to the main form. | Create a form that contains a subform by using the Form Wizard |
| You want to use an existing form as the main form, but you want Access to create a new subform and add it to the main form. | Add one or more subforms to an existing form by using the Subform Wizard |
| You want to use an existing form as the main form, and you want to add one or more existing forms to that form as subforms. | Create a subform by dragging one form onto another |
Create a form that contains a subform by using the Form Wizard
Important: Forms that you create by using the Form Wizard are not compatible with the Publish to Access Services feature. However, you can still use the forms when you have the database open in the Access program.
This procedure creates a new form and subform combination by using the Form Wizard. This is also the quickest way to get started if you have not already created the forms that you want to use as the main form or the subform.
-
On the Create tab, in the Forms group, click Form Wizard.
Note: If you are working in a web database, the Form Wizard command is located under Client Forms.
-
On the first page of the wizard, in the Tables/Queries drop-down list, select a table or query. For this example, to create an Employees form that displays orders for each employee in a subform, we will select Table: Employees (the "one" side of the one-to-many relationship).
Note: It does not matter which table or query you choose first.
-
Double-click the fields that you want to include from this table or query.
-
On the same page of the wizard, in the Tables/Queries drop-down list, select another table or query from the list. For this example, we will select the Orders table (the "many" side of the one-to-many relationship).
-
Double-click the fields that you want to include from this table or query.
-
When you click Next, assuming that you set up the relationships correctly before you started the wizard, the wizard asks How do you want to view your data? - that is, by which table or query. Select the table on the "one" side of the one-to-many relationship. For this example, to create the Employees form, we will click by Employees. The wizard displays a small diagram of a form. The page should resemble the following illustration:
The box in the lower portion of the form diagram represents the subform.
Note: If the wizard does not ask How do you want to view your data?, that means that Access did not detect a one-to-many relationship between the tables or queries that you selected. The wizard will continue, but Access will not add a subform to the form. You might want to click Cancel and examine your table relationships before you continue. Find links to more information about relationships in the See Also section.
-
At the bottom of the wizard page, select Form with subform(s), and then click Next.
-
On the What layout would you like for your subform? page, click the layout option that you want, and then click Next. Both layout styles arrange the subform data in rows and columns, but a tabular layout is more customizable. You can add color, graphics, and other formatting elements to a tabular subform, whereas a datasheet is more compact, like the datasheet view of a table.
-
On the last page of the wizard, type the titles that you want for the forms. Access names the forms based on the titles that you type, and labels the subform based on the title that you type for the subform.
-
Specify whether you want to open the form in Form view, so that you can view or enter information, or in Design view, so that you can modify its design, and then click Finish.
Access creates two forms - one for the main form that contains the subform control, and one for the subform itself.
Add one or more subforms to an existing form by using the SubForm Wizard
Use this procedure to add one or more subforms to an existing form. For each subform, you can choose to have Access create a new form or use an existing form as the subform.
-
Right-click the existing form in the Navigation Pane, and then click Design View .
-
On the Design tab, in the Controls group, click the down-arrow to display the Controls gallery, and ensure that Use Control Wizards is selected.
-
On the Design tab, in the Controls gallery, click the Subform/Subreport button.
-
Click on the form where you want to place the subform.
-
Follow the directions in the wizard.
When you click Finish, Access adds a subform control to your form. If you chose to have Access create a new form for the subform instead of using an existing form, Access creates the new form object and adds it to the Navigation Pane.
Create a subform by dragging one form onto another
Use this procedure if you want to use an existing form as a main form, and you want to add one or more existing forms to that form as subforms.
-
In the Navigation Pane, right-click the form that you want to use as the main form, and then click Layout View.
-
Drag the form that you want to use as the subform from the Navigation Pane onto the main form.
Access adds a subform control to the main form and binds the control to the form that you dragged from the Navigation Pane. Access also tries to link the subform to the main form, based on the relationships that have been defined in your database. -
Repeat this step to add any additional subforms to the main form.
-
To verify that the linking was successful, on the Home tab, in the Views group, click View, click Form View, and then use the main form's record selector to advance through several records. If the subform filters itself correctly for each employee, then the procedure is complete.
If the previous test does not work, Access was unable to determine how to link the subform to the main form, and the Link Child Fields and Link Master Fields properties of the subform control are blank. You must set these properties manually by doing the following:
-
Right-click the main form in the Navigation Pane, and then click Layout View.
-
Click the subform control one time to select it.
-
If the Property Sheet task pane is not displayed, press F4 to display it.
-
In the Property Sheet, click the Data tab.
-
Click the Build button
next to the Link Child Fields property box.The Subform Field Linker dialog box appears.
-
In the Master Fields and Child Fields drop-down lists, select the fields that you want to link the forms with, and then click OK. If you are not sure which fields to use, click Suggest to have Access try to determine the linking fields.
Tip: 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 form or child form to help make sure that the linking field is in it. For example, if the form is based on a query, you should make sure that the linking field is present in the query results.
-
Save the main form, switch to Form view, and then verify that the form works as expected.
Change the default view of a subform
Note: The procedure in this section does not apply to forms that are compatible with Access Services.
When you add a subform to a form, the subform/subreport control displays the subform according to the subform's Default View property. This property can be set to the following values:
-
Single Form
-
Continuous Forms
-
Datasheet
-
PivotTable
-
PivotChart
-
Split Form
When you first create a subform, this property may be set to Continuous Forms or perhaps Single Form. However, if you set the Default View property of a subform to Datasheet, then the subform will display as a datasheet on the main form.
To set the Default View property of a subform:
-
Close any open objects.
-
In the Navigation Pane, right-click the subform and then click Layout View.
-
If the Property Sheet is not already displayed, press F4 to display it.
-
In the drop-down list at the top of the Property Sheet, make sure Form is selected.
-
On the Format tab of the Property Sheet, set the Default View property to the view you want to use.
Note: If the view you want to use is not in the list, make sure the "Allow…View" property for the view is set to Yes. For example, if you want to specify Datasheet as the default view for the form, make sure that the Allow Datasheet View property is set to Yes.
-
Save and close the subform, and then open the main form to check the results.
Add related data to a form without creating a subform
Note: The procedure in this section does not apply to forms that are compatible with Access Services.
It is not always necessary to create a separate form object to display related data. For example, if you are working on a form in Layout view or Design view and you drag a table or query from the Navigation Pane to the form, Access creates a subform/subreport control that displays the data in that object. The object's Default View property determines how the data is displayed. Usually, this is set to Datasheet view, but you can also set the Default View property of a table or query to PivotTable or PivotChart, giving you more flexibility in displaying related data on forms.
Note: Changing the Default View property for a table or query determines how it is displayed whenever it is opened, whether you open it from the Navigation Pane or view it in a subform/subreport control. Because changing the view settings for a table can sometimes cause confusion when opening the table from the Navigation Pane, we recommend using a query for this procedure instead of a table.
Example: Add a linked PivotTable query to a form
Suppose that you have a main form that displays salesperson information, and you want to add a linked PivotTable to the form that summarizes each salesperson's sales across several regions and time periods. The general process would be as follows:
-
Create the main form that displays the salesperson information.
-
Create a query that contains the sales information. In this example, the query would likely contain the dollar amount, region, date, and the salesperson ID.
-
If the Property Sheet is not already displayed, press F4 to display it.
-
Click in a blank area above the query grid to make the Property Sheet display Query Properties.
-
On the Format tab of the Property Sheet, set the Default View property to PivotTable. Also, set the Allow PivotTable View property to Yes.
-
On the Home tab, in the Views group, click View, and then click PivotTable View.
-
Create the PivotTable view to display the information the way you want it.
For more information about creating a PivotTable view, see the video Pivot your data in Access. -
Save and close the query.
-
In the Navigation Pane, right-click the main form and then click Layout View.
-
Drag the PivotTable query from the Navigation Pane to the form.
-
Select the subform/subreport control that contains the PivotTable.
-
If the Property Sheet is not already displayed, press F4 to display it.
-
On the Data tab of the Property Sheet, make sure that the Link Master Fields and Link Child Fields properties are set correctly. In this example, the ID of the salesperson defines the relationship between the salesperson table and the sales table.
-
Save the main form, and then switch to Form view.
As you move from one salesperson record to the next, the PivotTable should change to reflect each salesperson's sales data.
No comments:
Post a Comment