Totalling amounts in a sub-sub-form 2

[DoctorJDM]

Have a form that records events, the people taking part in a first subform, and the amounts collected by each person in a sub-sub form. So the hierarchy is

frmEventName - single form view
contains sfmParticipant - single form view
contains sfmParticipantAmounts - datasheet view

On the main form frmEventName I'd now like to show the total of all subtotals for all participants.

I can get the total for a participant to show on sfmParticipant by using an unbound field ParticipantTotal and setting its source to be

=Forms!frmEventName.sfmParticipant.form.sfmParticipantAmounts.form.AmountsTotal

where AmountsTotal is the sum of amounts, placed in the footer of sfmParticipantAmounts.

How can I now sum these participant totals given that subform sfmParticipants has to be in single form view since it contains its own subform?

 

[Remou]

DSum may suit.

 

[TheAceMan1]

How are ya DoctorJDM . . .

The Idea is to [blue]sum the entire table[/blue] underlying [blue]sfmParticipantAmounts[/blue]. Try the following using SQL, should provide faster execution for large record counts.

In the code module of the main form, copy/paste the following function ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]Public Function ParticipentsTotal()
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT Sum([purple][b][i]FieldName[/i][/b][/purple]) AS Tlt " & _
         "FROM [purple][b][i]TableName[/i][/b][/purple];"
   Set rst = db.OpenRecordset(SQL, dbReadOnly)
   
   ParticipentsTotal = rst!Tlt
   
   Set rst = Nothing
   Set db = Nothing
   
End Function[/blue]

Then set the [blue]control source[/blue] of the textbox to:

[blue]   =ParticipentsTotal()[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[DoctorJDM]

Thanks guys, you're always a terrific help.

AceMan - nice technique, thanks. It gives the total for all events, which is very useful. I also need to show the total for a particular event on the main form, so ...

Remou - DSum worked just fine after creating a query to link participants and amounts then limiting the sum to the current event as below.

=DSum("InstallmentAmount","qryEventParticipantAmounts","EventID=forms!frmEventsRecord.EventID")

 

[TheAceMan1]

DoctorJDM . . .

For a particular event, the SQL reverts to:

[blue]   SQL = "SELECT Sum(InstallmentAmount) AS Tlt " & _
         "FROM qryEventParticipantAmounts " & _
         "WHERE [EventID] = " & Me!EventID & ";"[/blue]

Remember this if large record counts start slowing you down . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886