Dsum Re-calc of subform values

[Guest]

I have a few DSum fields on a parent form which calculate totals based on a
subform in Designsheet view, so, as the user adds "transactions" (the
subform), I want to show the total on the parent form. I tried re-query but
that didnt work well, is there any other way to update the fields on the
parent form? or specifically just the calcuated controls?

tia.

 

[John Vinson]

I have a few DSum fields on a parent form which calculate totals based on a
subform in Designsheet view, so, as the user adds "transactions" (the
subform), I want to show the total on the parent form. I tried re-query but
that didnt work well, is there any other way to update the fields on the
parent form? or specifically just the calcuated controls?

It may be better to avoid DSum() entirely in this context. Instead,
put textboxes on the subform's Footer with control source

=Sum([fieldname])

If one such textbox is named txtSumA, you can then set the control
source of a mainform textbox to

=subMySubform.Form!txtSumA

where subMySubform is the name of the Subform control on the mainform
(which might or might not be the same as the name of the form within
that control).

John W. Vinson[MVP]

 

[Guest]

I have to use Dsum to limit the type of transactions I am Summing. I already
have that control on the footer of the subform and copied it to the parent
form.

I tried your suggestion but keep getting #error. You said the subform name
might not be the same as its "base name"...how do I tell if it's different. I
don't remember re-naming it.

Thanks.

I have a few DSum fields on a parent form which calculate totals based on a
subform in Designsheet view, so, as the user adds "transactions" (the
subform), I want to show the total on the parent form. I tried re-query but
that didnt work well, is there any other way to update the fields on the
parent form? or specifically just the calcuated controls?

It may be better to avoid DSum() entirely in this context. Instead,
put textboxes on the subform's Footer with control source

=Sum([fieldname])

If one such textbox is named txtSumA, you can then set the control
source of a mainform textbox to

=subMySubform.Form!txtSumA

where subMySubform is the name of the Subform control on the mainform
(which might or might not be the same as the name of the form within
that control).

John W. Vinson[MVP]

 

[Guest]

I'm getting an error because the subform field is in error. I thought it was
the same as the one on the parent form. I'll try to correct this first and
get back to you.

I have a few DSum fields on a parent form which calculate totals based on a
subform in Designsheet view, so, as the user adds "transactions" (the
subform), I want to show the total on the parent form. I tried re-query but
that didnt work well, is there any other way to update the fields on the
parent form? or specifically just the calcuated controls?

It may be better to avoid DSum() entirely in this context. Instead,
put textboxes on the subform's Footer with control source

=Sum([fieldname])

If one such textbox is named txtSumA, you can then set the control
source of a mainform textbox to

=subMySubform.Form!txtSumA

where subMySubform is the name of the Subform control on the mainform
(which might or might not be the same as the name of the form within
that control).

John W. Vinson[MVP]

 

[Guest]

I got the DSUM working on the subform, but, when I add or modify a
transaction amount the field goes to #error. Is this standard behavior? Is
there a better way I should be calculating these values rather than using
calculated controls?

thx.

I have a few DSum fields on a parent form which calculate totals based on a
subform in Designsheet view, so, as the user adds "transactions" (the
subform), I want to show the total on the parent form. I tried re-query but
that didnt work well, is there any other way to update the fields on the
parent form? or specifically just the calcuated controls?

It may be better to avoid DSum() entirely in this context. Instead,
put textboxes on the subform's Footer with control source

=Sum([fieldname])

If one such textbox is named txtSumA, you can then set the control
source of a mainform textbox to

=subMySubform.Form!txtSumA

where subMySubform is the name of the Subform control on the mainform
(which might or might not be the same as the name of the form within
that control).

John W. Vinson[MVP]

 

[Guest]

John, I took your advice, moved the fields to the subform and am using your
syntax. One problem, before I enter any rows in the subform the values are
#error. I am using NZ around the Dsum, and as soon as I enter one row the
various fields go to 0. How can I trap the #error on the main form and skip
over it so the debugger doesn't come up? Otherwise, things are working fine

Thanks.

HB

I have a few DSum fields on a parent form which calculate totals based on a
subform in Designsheet view, so, as the user adds "transactions" (the
subform), I want to show the total on the parent form. I tried re-query but
that didnt work well, is there any other way to update the fields on the
parent form? or specifically just the calcuated controls?

It may be better to avoid DSum() entirely in this context. Instead,
put textboxes on the subform's Footer with control source

=Sum([fieldname])

If one such textbox is named txtSumA, you can then set the control
source of a mainform textbox to

=subMySubform.Form!txtSumA

where subMySubform is the name of the Subform control on the mainform
(which might or might not be the same as the name of the form within
that control).

John W. Vinson[MVP]

 

[Guest]

If anyone has made it this far, I figured out the way to get the DSum values
to re-calculate when there is a data change....use me.refresh (assuming the
dsums are on the same form), on any event that you want to re-calc the value.