Create and use subreports

Subforms on a report

A main report can include subforms in addition to subreports, and it can include as many subforms and subreports as you want. Additionally, a main report can contain up to seven levels of subforms and subreports. For example, a report can contain a subreport, and that subreport can contain a subform or a subreport, and so on, up to seven levels deep.

If you add a subform to a report and then open the report in Report view, you can use the subform to filter and navigate through records. Visual Basic for Applications (VBA) code and embedded macros that are attached to the form and its controls will still run, although some events are disabled in this context. You cannot add, edit, or delete records by using a subform on a report.

Linking a report to a subform or subreport

When you insert a subform or a subreport that contains information that is related to data in the main report, the subreport control must be linked to the main report. The link ensures that the records displayed in the subform or subreport correspond correctly to the records printed in the main report.

When you create a subform or subreport by using a wizard or by dragging an object from the Navigation Pane to a report, Access automatically links the subform or subreport to the main report if either of the following conditions is met.

• You define relationships for the tables you select, or you define relationships for the tables underlying the queries you select.

• The main report is based on a table with a primary key, and the subform or subreport is based on a table that contains a field with the same name as that primary key and has the same or a compatible data type. For example, if the primary key of the table underlying the main report is an AutoNumber field, and its FieldSize property is set to Long Integer, the corresponding field in the table underlying the subform or subreport must be a Number field with its FieldSize property set to Long Integer. If you select a query or queries as the record source of a subform or subreport, the underlying tables in the query or queries must meet the same conditions.

Top of Page

Use the Subreport Wizard to create a subreport

This procedure assumes that you already have a report to which you want to add a subreport. For more information about creating a report, see the articles Create a simple report or Create a grouped or summary report.

1. In the Navigation Pane, right-click the report to which you want to add a subreport, and then click Design View.

2. On the Design tab, in the Controls group, open the Controls Gallery by clicking the down-arrow at the lower right corner:

   

3. In the menu that appears, ensure that Use Control Wizards is selected.

4. Open the Controls Gallery again, and then click Subform/Subreport.

5. On the report, click where you want to place the subreport.

6. If a security notice dialog box appears, click Open to start the wizard.

7. On the first page of the Subreport Wizard, if you want to create a new subform or subreport and base it on a table or query, click Use existing Tables and Queries. If there is an existing report or form that you want to use as the subreport, click Use an existing report or form, select the report or form in the list, and then click Next.

   

   Note:  If there are no other reports or forms in the database, the list will be blank, and the Use an existing report or form option will not be available.

8. If you chose Use an existing report or form on the previous page of the wizard, skip this step and go directly to step 9. Otherwise, from the Tables/Queries list, select the table or query that contains the fields that you want included on the subreport, and then double-click the fields that you want in the Available Fields list to add them to the subreport.

   

   If you plan to link the subreport to the main report, be sure to include the field or fields that will be used to create the link, even if you don't want them to be displayed. Typically, the linking field is an ID field. In the preceding illustration, the Orders table is the record source for the subreport, and the Customers table is the record source for the main report. Because the Orders table is related to the Customers table by the Customer ID field, that field is added to the Selected Fields list.

   Note: You can include fields from multiple tables and queries on your subreport. When you finish adding fields from one table, select the next table or query from the Tables/Queries list and then add the fields that you want.

   Click Next to continue.

9. On this page of the wizard, you determine how to link the subreport to the main report. If Access finds fields that appear to be suitable for linking the subreport to the main report, the wizard displays a list of possible linking suggestions.

   

   You can select the linking suggestion that seems most appropriate for your situation or, if you do not want the subreport to be linked to the main report, select None. If you want to link the subreport to the main report, but none of the suggestions seem appropriate, click Define my own.

   Note: If the wizard cannot find suitable fields for linking, it does not provide a list of linking suggestions and automatically selects the Define my own option.

   

   When the Define my own option is selected, the wizard displays two sets of lists.

   • Under Form/report fields, select the field or fields from the main report that you want to use to link the main report to the subform or subreport. You can select up to three fields, and each field you select must match a related field in the subform's or subreport's data source.

   • Under Subform/subreport fields, select the corresponding field or fields from the subform or subreport that link to the main report fields that you selected.

   • To leave the subform or subreport unlinked to the main report, ensure that all the lists are empty.

     Click Next to continue.

10. On the last page of the wizard, type a name for the subform or subreport, or just click Finish to accept the default.

    

Access adds a subreport control to your report and binds the control (that is, it sets the control's Source Object property) as follows:

• If you selected Use an existing report or form on the first page of the wizard, Access binds the subreport control to the report or form that you specified.

• If you selected Use existing Tables and Queries on the first page of the wizard, Access creates a new report object in the Navigation Pane and then binds the subreport control to it. The name of the new report object is the same as the name you typed on the last page of the wizard.

Add a table, query, form, or report to a report as a subreport

A quick way to add a subreport to a report is to open the main report in Layout view or Design view, and then drag an object to it from the Navigation Pane. If you want the subreport to be linked to the main report, ensure that the underlying record sources are related and that the record sources include the fields that will be used to link the subform or subreport to the main report.

1. In the Navigation Pane, right-click the report you want to use as the main report, and then click Layout View or Design View on the shortcut menu.

2. Drag a table, query, form, or another report from the Navigation Pane to the section of the main report where you want the subreport to appear.

Access does one of the following:

• If Access can determine how to link the two objects, Access adds a subreport control to the report. If you add a form or report, Access binds the subreport control to that object. If you add a table or query, Access first creates a report object and then binds the subreport control to that new object.

• If Access cannot determine how to link the two objects, the Subreport Wizard appears. To continue, follow the procedure in the section Use the Subreport Wizard to create a subreport, starting with step 9.

  After you complete the wizard, Access adds the subreport to the report.

Use the following procedure to double-check that the subform or subreport is correctly linked to the main report.

1. Click the subreport control to select it.

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

3. On the Data tab of the property sheet, examine the Link Master Fields and Link Child Fields properties.

   • For an unlinked subform or subreport, both of these properties should be blank.

   • For a linked subform or subreport, the Link Master Fields and Link Child Fields properties should display the field or fields that relate the two objects together. For example, if the main report displays information from the Employees table and the subform or subreport displays information from the Orders table, the Link Master Fields property should display the ID field from the Employees table and the Link Child Fields property should display the Employee ID field from the Orders table.

You might discover that these properties need to be edited for the subform or subreport to work properly. Use the following procedure.

1. On the Data tab of the property sheet, click the Link Master Fields property box, and then click the Build button .

   The Subreport Field Linker dialog box appears.

   

2. In the Master Fields and Child Fields lists, select the fields that you want to link the reports with. If you are not sure which fields to use, click Suggest to make Access try to determine the linking fields. When you finish, click OK.

   If you do not see the field that you want to use to link the report, you need to edit the record source of the main report or of the subform or subreport to ensure that it contains the linking field. For example, if the report is based on a query, you should ensure that the linking field is present in the query results.

3. Save the main report and switch to Report view, and then verify that the report works as you expect.

Add a table or query to a report as a datasheet

A datasheet is a simple visual representation of data, similar to a spreadsheet. Each column in a datasheet represents a field in the source table or query, and each row represents a record. You can use the Subform/Subreport control to display a datasheet on a report. This is a good technique to use in situations where you want a compact display of data but do not need the formatting capabilities of a form or report object. To add a datasheet to a report:

1. In the Navigation Pane, right-click the report you want to use as the main report, and then click Design View on the shortcut menu.

2. On the Design tab, in the Controls group, open the Controls gallery by clicking the down-arrow at the lower right corner:
   
   
   

3. In the menu that appears, ensure that Use Control Wizards is not selected.

4. Open the Controls Gallery again, and then click Subform/Subreport.

5. On the report, click where you want to place the subreport.

6. If the Subreport Wizard starts, click Cancel to close it.

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

8. On the report, click the new subreport control to select it.

9. On the Data tab of the property sheet, click the arrow in the Source Object property box, and then click the table or query that you want to display in the subreport control. For example, to display the Orders table, click Table.Orders.

   Access attempts to link the datasheet to the main report, based on the relationships that were defined in the database.

10. On the Data tab of the property sheet, examine the Link Master Fields and Link Child Fields properties.

    • For an unlinked datasheet, ensure that both of these properties are blank.

    • For a linked datasheet, ensure that the Link Master Fields and Link Child Fields properties display the field or fields that relate the main report to the datasheet. For example, if the main report displays information from the Employees table and the datasheet displays information from the Orders table, the Link Master Fields property should display the ID field from the Employees table, and the Link Child Fields property should display the Employee ID field from the Orders table.

In some cases, you might need to set the link field properties yourself. You can do this by using the following procedure.

1. On the Data tab of the property sheet, click the Link Master Fields property box, and then click the Build button .

2. If a security notice dialog box appears, click Open.

   The Subreport Field Linker dialog box appears.

   

3. In the Master Fields and Child Fields lists, select the fields that you want to use to link the main report to the datasheet. If you are not sure of which fields to use, click Suggest to make Access try to determine the linking fields. When you finish, click OK.

   If you do not see a field that you want to use for linking, you need to edit the record source of the master report or the datasheet to ensure that it contains the linking field. For example, if the datasheet is based on a query, you should ensure that the linking field is present in the query results.

4. Save the main report and switch to Report view, and then verify that the report works as you expect.

Top of Page

Open a subform or subreport in a new window in Design view

To make design changes to a subform or subreport while you are working on its main report in Design view, you can open the subform or subreport in its own window.

1. Click the subform or subreport to select it.

2. On the Design tab, in the Tools group, click Subreport in New Window.

Display a total from a subreport on the main report

Suppose you use a subreport named Orders subreport which contains a text box named Shipping Fee Total, and that text box calculates the sum of the Shipping Fee column. To display the sum from the subreport on the main report, you must add a text box to the main report and then use an expression to refer to the Shipping Fee Total text box on the subreport. You can do this by using the following procedure.

1. Right-click the main report in the Navigation Pane, and then click Design View on the shortcut menu.

2. On the Design tab, in the Controls group, click Text Box.

3. On the main report, click where you want to place the new text box.

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

5. On the Data tab of the property sheet, in the Control Source property box, type the following expression.

   =IIf(IsError([Orders subreport].[Report]![Shipping Fee Total]),0,[Orders subreport].[Report]![Shipping Fee Total])

   Notes: 

   • In this example, you could use the more simple expression =[Orders subreport].[Report]![Shipping Fee Total] but then, if the subreport doesn't contain any data, the control on the main report displays #Error. Using the IsError function within the IIf function, as shown in the first expression, ensures that the text box on the main report displays a zero (0) if the subreport does not return any data.

   • You can use the Expression Builder to create the expression by clicking the Build button in the Control Source property box.

6. On the Format tab of the property sheet, set the Format property to the appropriate value (in this case, Currency).

7. Save the report, and then switch to Report view to verify that the calculation works as you expect.

Top of Page