Display subforms in form

In “Access”, we can perform calculations in a “Subform”, which can be displayed in the “Main form”.

In the example below, I use the form with the “Subform” that we made in the previous lesson. To navigate between the different students in the “Main form”, use the navigation bar (A) of the “Main form”. To navigate between the different details of the student, use the navigation bar (B) of the “Subform”.

We will continue with the calculated field in the “Main form”. You have the ability to calculate the average (or any other calculation) of all tests in the “Subform” and display it in the “Main form”.

Proceed as follows:

First select the “Design View” in the “Ribbon”.
In the ‘Results’ “Subform”, add a text box in the footer section, by clicking on the “Text Box” in the “Ribbon” (1) and clicking and dragging it to the “Subform”‘s footer section.

Why do all this in the footer section of the “Subform”?
If we enter this in the “Details” section, this is also displayed as another field next to each record in the “Subform” and that is not what we want.
You give it the “Average” label (2) and in the text box type the “Formula” = Avg ([score]) (3).

For the text box itself, we give an appropriate name, such as “avg score”.

Since we need to refer to this text later, it is easier when we know what this text box is named.
Select this textbox and click the “Property sheet” button in the “Ribbon” (1) to open the properties window.
Select the “Other” (2) tab and type “avg score” in the “Name” (3).
The first part is done.
Since the “Subform” is displayed in “Datasheet view”, this formula is not visible, so we need to have a text box in the “Main Form” which refers to this formula from the “Subform”.

Proceed as follows:
First select the “Main form”.
Insert a text box in the “Main form” and give it the “Average” (1) label.
Select the text box itself (2) and click the “Properties Sheet” button (3) in the “Ribbon”, to open it.
In the “Properties window” choose the “Data” (4) tab and click the button with the icon on the far right of the “Control Source” (5) box.

This opens the “Expression Builder”. In this dialog box, select the text box that you just created in the “Subform”. This can be found if you double-click on the name of the “Main form” in which you are working. In this case, it is the “Students” (1) form. Under this form, you see the “Results” “Subform” so, select it (2). In the middle section of the dialog box, double-click the newly created text field called “avg score” (3).

Click the OK button (4).
And save the changes.
Go to “Form view” and see the result.
The average score is added to the “Main form” (5).

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats