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:
The Order Totals at the bottom of the 'Sizes' subform.
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.
