well, to get a total of fees paid for a participant, you can use a DSum()
function in an unbound textbox control on your (main) form, as
=DSum("[AmountPaidFieldName]", "TableThatSubformIsBasedOnName",
"[ForeignKeyFieldInTableName] = " & [PrimaryKeyFieldInMainFormTableName])
the above all goes on one line, of course, and you'll need to substitute the
correct field, table, and control names. if you're not familiar with domain
aggregate functions such as DSum(), suggest you read up on the Help topic(s)
to understand how they work.
since you're displaying mutiple records at once, the DSum() function has to
run for each record - may be slow to populate. an alternate approach might
be to write a Totals query to get the total fees paid, grouped by
participant. then create a query based on the participants table and the
Totals query, with a LEFT JOIN from that table to the Totals query, and the
Total fees field added to the query design grid. then base the (main) form
on the second query. i haven't tested it, but i think with a LEFT JOIN the
query will be updateable, in case you need to add/edit participant records.
now, the above will get you the total you need without needing to use a
subform, one way or the other. since a subform isn't available in a form
that's in Datasheet view, i'm assuming that by "drill down", you mean that
you're going to open a separate form window displaying the detail records,
as needed. (that separate form, by definition, is not a "subform", btw.)
hth
Don Rudd said:
Thanks Tina. The main form displays participants in an activity. The
subform displays financial transactions (date, amount, receipt number for the
participant's payment of fees/refunds for the activity - their may be more
than 1 payment/refund per participant). I total up fees paid for the
participant in the subform and (would like) to display the total in the main
form. Then the main form will display the summary information for the
participant and the user can drill down for the details of financial
transactions in the subform if they need to (if not, the details remain
hidden).
Don