Total subform items on main form

[jazminecat]

I have a form with three subforms. each of these subforms contains charges to an account. Not all records have charges on all subforms, though. So some records will have no charges at all, some will have charges from one, two, or all three. It varies.

I have a text field on the main form into which I want to sum the total of all charges for any given record. It is an unbound text box, and has this code in it's control source:

=[QryCommissary subform].Form!SumOfAmount+[QryBookingFees subform].Form!Amount+[QryDental subform1].Form!Amount

where QryCommissary subform is one form, QryBookingFees subform is the second form, and QryDental subform1 is the third form.

When I load the form I get #error on the text field, no matter what record I am on.

Any thoughts? I'm not averse to handling this field using vba instead, i'm just stuck on how.

thanks!

 

[VicRauch]

jazminecat: You should be able to reference that field name from the footer in your subform.
Remou: Great job!

 

[ssatech]

Have you tried setting the Default Value of the text field to:

Default Value....... =0

 

[Remou]

I have just noticed that the dratted cut and paste got me again.

Private Sub Form_Current()
Dim intAddBF as Integer
Dim intAddCom as Integer
Dim intAddDent as Integer

Me.Recalc
'First subform
If Me.[SubBookingFees].[Form].RecordsetClone.RecordCount = 0 Then
    intAddBF = 0
Else
    intAddBF = Me.[SubBookingFees].[Form].[Amount]
End If

'Second subform
If Me.[SubCommissary].[Form].RecordsetClone.RecordCount = 0 Then
    intAddCom = 0
Else
'TotalAmount should be the name of the control with 
'the total from the Subcommissary form footer
'as mentioned by Vic Rauch
    intAddCom = Me.[SubCommissary].[Form].[TotalAmount]
End If

'Third subform
If Me.[SubDental].[Form].RecordsetClone.RecordCount = 0 Then
    intAddDent = 0
Else
    intAddDent = Me.[SubDental].[Form].[Amount]
End If

'Guessing the name of the textbox on the main form
Me.[TotalAmount] = intAddBF + intAddCom + intAddDent
End Sub

Vic Rauch: Thank you.

 

[Remou]

And the variables should probably be currency if we are talking about monetary amounts:
Dim intAddBF As Currency
Dim intAddCom As Currency
Dim intAddDent As Currency

 

[jazminecat]

the total in the footer is indeed called TotalAmount. Defaults are set to 0.