running total x 2 plus calculation

[Guest]

I have a form with two subforms. I need to sum amounts in both forms,
calculate the difference, and then store this amount in the table accessed by
the main form. How can I best make this happen?

 

[roadie.girl]

In the main form make a text box.
put it's "Data" to be = SUM([Forms]![[mySubForm1]![totalforthisform] -
[Forms]![mySubForm2]![totalforthisform])

Me.TotalInTable = Me.txtBoxOnMainForm

 

[John Vinson]

I have a form with two subforms. I need to sum amounts in both forms,
calculate the difference, and then store this amount in the table accessed by
the main form. How can I best make this happen?

Put a textbox on the Footer of each subform with a Control Source

=Sum([fieldname])

Let's say you have SubformA and SubformB, with txtTotal on each. On
the mainform put a control

=SubformA.Form!txtTotal - SubformB.Form!txtTotal

This will display the difference.

It's probably a VERY BAD IDEA to store that difference in a table.
What if one of the records on one of the related tables gets deleted
or edited? Your stored sum is now WRONG, with no easy way to detect
that fact. It's possible to use some VBA code to store the value, but
it will be difficult since you'll need some way to be sure that all
records have been added (and edited) in the subforms before you try to
store it. I'd really suggest just recalculating it (as described, or
with a Totals query) as needed, and NOT storing it.

John W. Vinson[MVP]

 

[Guest]

Thanks all.

I have a form with two subforms. I need to sum amounts in both forms,
calculate the difference, and then store this amount in the table accessed by
the main form. How can I best make this happen?

Put a textbox on the Footer of each subform with a Control Source

=Sum([fieldname])

Let's say you have SubformA and SubformB, with txtTotal on each. On
the mainform put a control

=SubformA.Form!txtTotal - SubformB.Form!txtTotal

This will display the difference.

It's probably a VERY BAD IDEA to store that difference in a table.
What if one of the records on one of the related tables gets deleted
or edited? Your stored sum is now WRONG, with no easy way to detect
that fact. It's possible to use some VBA code to store the value, but
it will be difficult since you'll need some way to be sure that all
records have been added (and edited) in the subforms before you try to
store it. I'd really suggest just recalculating it (as described, or
with a Totals query) as needed, and NOT storing it.

John W. Vinson[MVP]

 

[Guest]

John,

I've decided to use the query approach and I have a running total of
subforms A and B and am able to calculate the difference. My difficulty is
now displaying the results (totals and calculation) on the main form. I've
attempted to use a subform C on the main form but it doesn't display anything
although the subform itself when opened in a new window via a macro works
just fine. I'd rather not open a new window to display totals.

What I would like is the ability to press a button and display both totals
and the difference on the main form. I hope this makes sense.

I have a form with two subforms. I need to sum amounts in both forms,
calculate the difference, and then store this amount in the table accessed by
the main form. How can I best make this happen?

Put a textbox on the Footer of each subform with a Control Source

=Sum([fieldname])

Let's say you have SubformA and SubformB, with txtTotal on each. On
the mainform put a control

=SubformA.Form!txtTotal - SubformB.Form!txtTotal

This will display the difference.

It's probably a VERY BAD IDEA to store that difference in a table.
What if one of the records on one of the related tables gets deleted
or edited? Your stored sum is now WRONG, with no easy way to detect
that fact. It's possible to use some VBA code to store the value, but
it will be difficult since you'll need some way to be sure that all
records have been added (and edited) in the subforms before you try to
store it. I'd really suggest just recalculating it (as described, or
with a Totals query) as needed, and NOT storing it.

John W. Vinson[MVP]

 

[John Vinson]

John,

I've decided to use the query approach and I have a running total of
subforms A and B and am able to calculate the difference. My difficulty is
now displaying the results (totals and calculation) on the main form. I've
attempted to use a subform C on the main form but it doesn't display anything
although the subform itself when opened in a new window via a macro works
just fine. I'd rather not open a new window to display totals.

What I would like is the ability to press a button and display both totals
and the difference on the main form. I hope this makes sense.

Ummm... my suggestion will do that without even a button, and I
certainly would not suggest "running" a query or opening another
window; nor is a third subform necessary.

Remember - the values that are being totaled are NOT coming from the
Form. The form is just a window; the data being summed *is in the
tables*. You can create a stored Totals query (referencing the form to
select the records pertaining to the currently viewed record), and use
DLookUp or DSum in the control source of a textbox on the form to
display the summmed value.

John W. Vinson[MVP]

 

[Guest]

Ahhh.. (light bulb). You're right. I'm making this more difficult than
necessary.

Thanks much.