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:

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.

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()
    Form_F_Order_Total_Price.Form.Requery
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.