databasedev.co.uk - database solutions and downloads for microsoft access

Displaying Subform Totals in a Main Form in Microsoft Access

In this article we are going to show you how to display totals taken from your subform, and display these in controls in your main form. We need to be able to reference the subform control from the main Microsoft Access form.

If you attempt to sum a subform field by using a calculated control on a main form, you may receive an error message in the main form control. For example, the following expression in a main form control generates a "#Error" error message:

=Sum(sfrmAlbumTracks.Form!txtLength)

We can see this in use on the following form, where we would like to show the Total Album Length on our main form by calculating the sum of the tracks displayed in the subform. The control with the error has been highlighted:

The form containing the calculated control, which is attempting to sum the values from the subform

To get around this problem, and to avoid returning an error with our calculation, we can sum the subform field by using a calculated control placed in the subform's form footer. If we do not wish to display this control, as we will be returning the result in the main form, we can set the controls property to Visible: No. We can then reference the calculated control from the main form to display the summed value.

See this article over at The Access Web for details of Referring to Form and Subform Properties and Controls

To solve this problem, we first add a calculated control in the footer of our Subform, which sums the subform's Track Length field to create a subtotal. It uses the following syntax:

=Sum([Length])

We can see this in the image shown below:

The subform, containing the calculated control within the form footer

We have named this control txtTotalTrackLengths and also set the control properties to Visible: No

Now, a textbox control on our main form refers to the subform's calculated control to gets its value to display.The main form control is called txtTotalAlbumLength and references the subform's calculated control. It uses the following syntax:

=[sfrmAlbumTracks].Form![txtTotalTrackLengths]

This is referring to the control (txtTotalTrackLengths) contained on the subform (sfrmAlbumTracks)

The main form, displaying the calculated control, returning the subforms subtotals

Using this reference allows the main form to display the subform's subtotal control while the actual totaling of subform values is performed by using a calculated control in the subform's form footer.