Total

[D B Lawson]

I have an order form with a subform. The subform is in datasheet view and has many entries. The link is the order number. The subform has a quantity field, unit price field and a field calculating the line total. How can I show the sum of the line totals for that order on the main form?

 

[Jack Cowley]

This article will show you how to refer to your field in your subform. http://support.microsoft.com/support/kb/articles/Q113/3/52.asp?LN=EN-US&SD=gn&FR=0
Using the Sum function you can display the total on your main form.

HTH,
Jack

 

[accesswatch]

In your Subform:
Create a text box called 'txtsfrmTotal' in the footer section of the sub form
(Note: Because you are displaying the sub form as a datasheet this new field will not be displayed on screen but you can still refrence it)
In the data controlsource of the new textbox 'txtsfrmTotal' enter

=Sum([unitprice]*[quantity])

Note:You need to SUM the [unitprice]* [quantity] to arrive at the sum field you cannot simply do a SUM of the calculated line field totals.

On the MAIN FORM create a textbox called
'txtOrderTotal'

in the data controlsource for this text box enter

=[sfrmDetails].[Form]![txtSfrmTotal]

Note: In this example the subform in called 'sfrmDetails' you will have to substitute the name of the sub form you are using.
Also note how the 'txtOrdertotal' is only updated when you move to another record.

An example of how to do this can be found in the 'Northwind Database' in the ORDERS Form

Also, as Jack Cowley has pointed out, more info can be obtained from the Knowledge base article Jack has refered to

Hope this helps

Trevor from Accesswatch.co.uk

 

[D B Lawson]

Many thanks for your help. Got it working. It's easy when you know how - I can't tell you how long I've been trying to get that right!!