<%dim crumb,crumb1 crumb="Form Subtotals" crumb1="Form Subtotals" %> Total/Subtotal Fields
Home  Fees/Services  Access Examples  Tutorials  Access Download  Articles  Search  Contact  Privacy  Links

Access Form Programming Tutorial
<% response.write crumb1 %>

Home > Microsoft Access Tutorials > Access Forms Tutorial <% response.write ">" & crumb %>
<% Gnum="9096031968" Ynum="7841" dim Page_Type Page_Type="Prog" response.write "" %>

Form Subtotal Fields
Master/Detail Forms
NotInList Event
Speed Up Forms
Unbound Form
Change Textbox Height
Get Website Data
Access Audit Trail

Form Query Parameter

Totals & Subtotals on Access Forms

Having totals at the bottom of a continuous form is pretty standard form programming in Microsoft Access. Grand totals that appear in the master form are however a slight twist and a little more complicated to implement. In this forms programming example we are using our customer order form.  Here is an overview look at the form:

There are three items that we are interested in:

  1. The Order Totals at the bottom of the 'Sizes' subform.

  2. The Total Price at the top right of the main form.

Note that the order totals are only for a single order item entry in Order Items sub form.  Note also that these two forms are continuous - which is not normal for an Access form.  We have already created an example which shows continuous-continuous master/detail forms.

Below is an overview of the same form in design view:

In the image above we are concentrating n the Order Total field in the 'Sizes' sub form.  We are showing the control source for this field in the property sheet:  =Sum(nz([qty],0)*nz([price],0))   This is simply the quantity times the price with a null zero function stuck in there so we don't get any errors.  That's all there is to a form total - just place the total field in the form footer - that's the only place it will work.

Now, moving on to the slightly more complicated presentation of the form Total as part of the main form information.  Note in the above image that the form total field is only visible as a shrunken subform above the Shipping Date field.

Below we have temporarily expanded this subform so you can see it better:

Subtotal Fields on Access Forms

Notice we have displayed the properties for the Total Price subform.  Below we have the query's sql text - it is simply a query to sum the product of Qty * Price grouped by Order_ID.   When we place this subform onto the main form we indicate the link master and link detail fields to be Order_ID so that the subform will be coordinated with the main order form.

Totals & Subtotal Fields

Last thing is we have added a bit of visual basic code to the Sizes subform so that the form Total field will be updated instantly when anything is changed in the Sizes subform:

Private Sub Form_AfterUpdate()
End Sub

Notice we are requerying theTotal sub form at the top of the form.  To reference the Total Price form in this manner you'll need to set its 'Has Module' property to Yes.

Microsoft Office Forms:
 MS Access 2003
 Access 2007
 Access 2010
 Access 2013

Contact Information

<% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %>