Displaying Subtotals and a Grand Total on a Form
(That Displays a One-to-Many Relationship)

By Tom Wickerath ~ Access MVP

Note: These instructions were written when I taught an Access course, using Microsoft Access 2000, at a local community college. They were written for use with a student database, "Bavant Marine Services," which was created by each student using step-by-step instructions in Project 1 of the book used with this course. An enhanced version of this database is available for download (295 KB) to follow along with these instructions.

The Technician Master form in Bavant Marine Services is used to display a one-to-many relationship.  On the "one" side, we see information about the technician. The "many" side of the relationship is displayed in a subform, which includes the marinas each technician services, along with the warranty and non-warranty amounts for each marina.  This document will explain how to include textboxes to display subtotals for each of these fields, as well as a grand total field.  To see an example of this, click here.  You can easily produce a grouped report to return this type of information, however, this does not allow one to see real-time changes to these amounts during the data entry phase.

To accomplish our goal, we take advantage of the fact that controls in a form's Header and Footer sections do not display in Datasheet View. Datasheet View is the spreadsheet-like view of a subform.  In order to see these controls' sections, you must be in Form View.

Figure 1: Datasheet view of the fsubMarinas subform

Figure 2: Design view of the fsubMarinas subform

Add two textbox controls in the Footer of your subform to sum the Warranty and NonWarranty fields. Give your new textboxes reasonable names, and set their control sources as indicated below:

Note that we must include the parentheses with the Sum function. The square brackets are used when referencing a field name.  Their use is required when special characters have been used. Record the names of your new controls.  You will need to know these names later on when you are setting the control sources for the textboxes on your main form, unless you use the Expression Builder.  There is no need to keep the labels that are automatically included when you create each textbox on the subform.

Figure 3:  Subform with two textbox controls added to Form Footer

Switch to Form View to test out your two new textboxes. You should see the sums for all records displayed, as shown below. In Datasheet View, your subform will look identical to Figure 1.

Figure 4:  Subform displayed in Form view to test the two new textboxes

We are now ready to add textboxes to our main form, which reference the values of our subtotal textboxes from the subform. One of the first things we need to do is to record the name of the subform object in design view. We are not interested in the name of the subform itself, shown as the "Source Object" on the Data tab, only the name of the subform object. (Note: It is not unusual to find that these names are identical.) We can learn this name by selecting this object, such that the sizing handles are shown around the entire object. Open your main form in design view, make sure you are viewing the properties, and then select this object as shown below:


Figure 5:  Selecting the Subform object to learn its name

Finally, we add three textbox controls on the form to display the warranty and non-warranty subtotals, along with the grand total.  You can use the same name, or a different name, for your subtotal textboxes as compared with the names you used in your subform. However, the Control Sources will now reference the name of the subform object and the names of the textboxes in your subform as shown below in general form:

=[NameOfSubformObject].[Form]![NameOfTextboxInSubform]

 The grand total textbox is calculated by simply adding the two subtotal textboxes on your main form.  You can add a line control to add visual clarity.

Your finished form should look something like the example shown below. As new marina records get added to the subform, the warranty and non-warranty subtotals and the grand total textboxes, shown circled below, will be updated. 

Other Pages at VB123.com That You May Want To Visit

Using Microsoft Access To Display Spatial Information
Tom's Article on Implementing a Successful Multi-user Access/JET Application
Duplicate Data Entry For Access
Track all changes made to a record in Microsoft Access

Click on the button for the next help page in this Access Loop.

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals