Bring total from subform to mainform display in datasheet view

[Guest]

A total of a field in a subform can be brought to a mainform via the
subform's footer but MS Access won't allow it to be displayed if the main
form is in datasheet view mode. Is there a way around this?

 

[tina]

it's not clear what you're wanting to display in the main form - the
subform? or the value from the field in the subform?

hth

 

[Guest]

Thanks Tina. I want to display the calculated value. I am able to display
it if the main form is in Single Form view but not in Datasheet view. I
notice the sample Nortwind database has the same problem when I change the
view mode of it's main form to Datasheet. (The control displays ERROR).

 

[tina]

okay. since you can't see a subform when the main form is in Datasheet view,
i'm wondering why you're using a subform at all. if you'll explain the
purpose, maybe we can suggest an alternative that will work for you.

hth

 

[Guest]

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

 

[tina]

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

 

[Guest]

Thanks again Tina. I tried both the DSUM approach and the separate summing
query approach and both weren't entirely suitable. I've opted to have 2 tab
control pages containing subforms of a main form - one displaying in Single
Form view the other in Datasheet view to provide the functionality I want.
By the way, I'm sure you already know it but you can have access a subform
from a form that's in datasheet view - you open the subform as needed by
clicking on the + icon in the (not sure what's it called - maybe "access a
subform" or my "drilling down") column of the datasheet.
Don

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

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

 

[tina]

By the way, I'm sure you already know it but you can have access a subform

from a form that's in datasheet view - you open the subform as needed by
clicking on the + icon in the (not sure what's it called - maybe "access a
subform" or my "drilling down") column of the datasheet.

well, i never allow subdatasheets in my tables, and i use subforms
exclusively in forms. your first post got me curious, so i tried to create a
form that showed that subdatasheet "+" column when the form was in Datasheet
view - and i couldn't figure out how to do it. (i'm using A2003, so i don't
know if that's an issue.) a subdatasheet in a form is not the same as a
subform, AFAIK, but since i don't use subdatasheets i'm really not up on the
subject.

at any rate, you came up with a solution that suits you, so that's good.

Thanks again Tina. I tried both the DSUM approach and the separate summing
query approach and both weren't entirely suitable. I've opted to have 2 tab
control pages containing subforms of a main form - one displaying in Single
Form view the other in Datasheet view to provide the functionality I want.
By the way, I'm sure you already know it but you can have access a subform
from a form that's in datasheet view - you open the subform as needed by
clicking on the + icon in the (not sure what's it called - maybe "access a
subform" or my "drilling down") column of the datasheet.
Don

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

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

:

okay. since you can't see a subform when the main form is in

Datasheet
view,

i'm wondering why you're using a subform at all. if you'll explain the
purpose, maybe we can suggest an alternative that will work for you.

hth


Thanks Tina. I want to display the calculated value. I am able to
display
it if the main form is in Single Form view but not in Datasheet

view.
I

notice the sample Nortwind database has the same problem when I

change
the

view mode of it's main form to Datasheet. (The control displays ERROR).

:

it's not clear what you're wanting to display in the main form - the
subform? or the value from the field in the subform?

hth


A total of a field in a subform can be brought to a mainform

via
the

subform's footer but MS Access won't allow it to be displayed

if
the

main
form is in datasheet view mode. Is there a way around this?

 

[Guest]

Thanks again for your help Tina. I see now we had a difference regarding
"subdatasheet" and "subform" terminology. FYI, have you had a look at "About
subdatasheets" in Help for Access 2003 (i'm using the same version)? It
shows the "expand/contract indicator" as they (sensibly) call my "drill-down
icon".

Don