Viewing the sum of Subform field in Mainform Using VBA Function

[mseo]

this is to explain how to view totals from our subform, and display it in textbox in your mainform. We need to be able to reference the subform control from the main form
first we can view the control's result that contains sum function in single form and continuous form normally using textbox =nz(Sum([quantity]),0)
but what about If we use subforms, this is the point
If we attempt to sum a subform field by using a calculated control on a main form, we will get an error in that control
although we have used the correct syntax for referencing it

=[Forms]![mainform name]![subform name]![name of a control that calculated sum in subform]

but it doesn't work in the correct sequence
so we have to create module level function

Function nnz(testvalue As Variant) As Variant
    If Not (IsNumeric(testvalue)) Then
        nnz = 0
    Else
        nnz = testvalue
    End If
End Function

then in the control in mainform that you need to view the sum of the subform field in

=nnz([Forms]![your mainform name]![your subform name]![the control that calculated sum in subform name])

and in the above reference I mentioned the mainform name although I don't have to do this because the track name autocorrect will get the name of the mainform but it is recommended to turn the name autocorrect off before referencing the function
because if the autocorrect is on and you don't reference the mainform name in the textbox, If you need to turn the track name autocorrect off for one reason or another in this case you will lose the reference and get an error
so from the begining
1-turn track name autocorrect off
2- otherwise reference the mainform name in your function
this should solve all the tricky points about displaying the sum of subform in mainform using textboxes controls

[MikeTheBike]

Hi

What you can do is put a total control on the sub form (as indicated in your post) with a name of, say, txtSubFormTotal , with a control source of
=nz(Sum([quantity]),0)

then on the main form add a control with a control source of
=[SubFormName].[Form].[txtSubFormTotal]

That should do it ??


MTB

p.s. the txtSubFormTotal does not have to be visible!!