subform total

[gator]

Can this or something like this be used to SUM a field from the subform
Child16?
Here is the Form

Form Name: frmForm
SubForm Name: Child16
SubForm field Name to be totaled: Balance
frmForm footer textbox: txtTotal
Child16 Source Object: UnionQuery

UnionQuery SQL:
SELECT FundDate, FundID, FundName, Balance FROM Funds2009
UNION SELECT FundDate, FundID, FundName, Balance FROM Funds2008
UNION SELECT FundDate, FundID, FundName, Balance FROM Funds2007
UNION SELECT FundDate, FundID, FundName, Balance FROM Funds2006;


Here is what I'm trying to use in txtTotal's Control Source property
=Sum([Me]![Child16].[Balance])

I have tried many variations of this and cannot get a number. Is there
anything close to this that will make the calculation on the subform on the
form?

What do I have to do to get txtTotal to update the Total based on what
Child16's content is?

 

[Klatuu]

Look in the Northwind sample database. There is an Orders form and an Orders
Subform. It does exactly that.

 

[gator]

I don't have a seperate subform that is independent of the Main Form. In
other words, in my Forms window of the Database, there is no Subform named
Child16.
I drug Child16 from the toolbox onto the form and canceled the wizrd and set
the Source Object as Query.FundsAllYears. So, there is no txtbox on a
subform anywhere that is already doing the calculation. Now if that is the
only way to get that calculation then I will add an independent subform that
does the calculation in it's footer (like Northwinds). I guess it seemed to
me that a subform total could be calulated directly on the Main form,
directly from the subform on the main form, and not necessarily from an
independent subform.

Look in the Northwind sample database. There is an Orders form and an Orders
Subform. It does exactly that.
--
Dave Hargis, Microsoft Access MVP

Can this or something like this be used to SUM a field from the subform
Child16?
Here is the Form

Form Name: frmForm
SubForm Name: Child16
SubForm field Name to be totaled: Balance
frmForm footer textbox: txtTotal
Child16 Source Object: UnionQuery

UnionQuery SQL:
SELECT FundDate, FundID, FundName, Balance FROM Funds2009
UNION SELECT FundDate, FundID, FundName, Balance FROM Funds2008
UNION SELECT FundDate, FundID, FundName, Balance FROM Funds2007
UNION SELECT FundDate, FundID, FundName, Balance FROM Funds2006;


Here is what I'm trying to use in txtTotal's Control Source property
=Sum([Me]![Child16].[Balance])

I have tried many variations of this and cannot get a number. Is there
anything close to this that will make the calculation on the subform on the
form?

What do I have to do to get txtTotal to update the Total based on what
Child16's content is?

 

[Klatuu]

The Northwind example is the best way I know to do it.
Can't you just add a control to the footer of your existing subform?
--
Dave Hargis, Microsoft Access MVP

I don't have a seperate subform that is independent of the Main Form. In
other words, in my Forms window of the Database, there is no Subform named
Child16.
I drug Child16 from the toolbox onto the form and canceled the wizrd and set
the Source Object as Query.FundsAllYears. So, there is no txtbox on a
subform anywhere that is already doing the calculation. Now if that is the
only way to get that calculation then I will add an independent subform that
does the calculation in it's footer (like Northwinds). I guess it seemed to
me that a subform total could be calulated directly on the Main form,
directly from the subform on the main form, and not necessarily from an
independent subform.

Look in the Northwind sample database. There is an Orders form and an Orders
Subform. It does exactly that.
--
Dave Hargis, Microsoft Access MVP

Can this or something like this be used to SUM a field from the subform
Child16?
Here is the Form

Form Name: frmForm
SubForm Name: Child16
SubForm field Name to be totaled: Balance
frmForm footer textbox: txtTotal
Child16 Source Object: UnionQuery

UnionQuery SQL:
SELECT FundDate, FundID, FundName, Balance FROM Funds2009
UNION SELECT FundDate, FundID, FundName, Balance FROM Funds2008
UNION SELECT FundDate, FundID, FundName, Balance FROM Funds2007
UNION SELECT FundDate, FundID, FundName, Balance FROM Funds2006;


Here is what I'm trying to use in txtTotal's Control Source property
=Sum([Me]![Child16].[Balance])

I have tried many variations of this and cannot get a number. Is there
anything close to this that will make the calculation on the subform on the
form?

What do I have to do to get txtTotal to update the Total based on what
Child16's content is?

 

[gator]

When you mention the existing subform there are two ways to understand what
that is.

1-a subform control on the Main form where a seperate independent form, that
is physically seperate from the main form, that can be inheritted into the
Main form.

2-a subform control on the Main form that does not inherit a seperate
independent form, but, that directly inherits a table/query.

The main difference between the two is that when a subform control is
dragged onto the form, the wizard automatically creates a seperate form, to
serve the subform, and then the new form can be modified to include a textbox
for a calculation. If however, the wizard is started and then cancelled, a
new form is not created as a seperate form, to serve the subform, thus a
calculation cannot be added to the subform. One can still set the Source
object to inherit a different form, a table or query but cannot set a
calculation on the Main form for a field on the subform if it inherits a
table/query without using a DSUM function grabbing the criteria directly from
the table/query, rather than the subform's content, as it exists on the Main
form.

The Northwind example is the best way I know to do it.
Can't you just add a control to the footer of your existing subform?
--
Dave Hargis, Microsoft Access MVP

I don't have a seperate subform that is independent of the Main Form. In
other words, in my Forms window of the Database, there is no Subform named
Child16.
I drug Child16 from the toolbox onto the form and canceled the wizrd and set
the Source Object as Query.FundsAllYears. So, there is no txtbox on a
subform anywhere that is already doing the calculation. Now if that is the
only way to get that calculation then I will add an independent subform that
does the calculation in it's footer (like Northwinds). I guess it seemed to
me that a subform total could be calulated directly on the Main form,
directly from the subform on the main form, and not necessarily from an
independent subform.

Look in the Northwind sample database. There is an Orders form and an Orders
Subform. It does exactly that.
--
Dave Hargis, Microsoft Access MVP


:

Can this or something like this be used to SUM a field from the subform
Child16?
Here is the Form

Form Name: frmForm
SubForm Name: Child16
SubForm field Name to be totaled: Balance
frmForm footer textbox: txtTotal
Child16 Source Object: UnionQuery

UnionQuery SQL:
SELECT FundDate, FundID, FundName, Balance FROM Funds2009
UNION SELECT FundDate, FundID, FundName, Balance FROM Funds2008
UNION SELECT FundDate, FundID, FundName, Balance FROM Funds2007
UNION SELECT FundDate, FundID, FundName, Balance FROM Funds2006;


Here is what I'm trying to use in txtTotal's Control Source property
=Sum([Me]![Child16].[Balance])

I have tried many variations of this and cannot get a number. Is there
anything close to this that will make the calculation on the subform on the
form?

What do I have to do to get txtTotal to update the Total based on what
Child16's content is?

 

[John W. Vinson]

Can this or something like this be used to SUM a field from the subform
Child16?
Here is the Form

Form Name: frmForm
SubForm Name: Child16
SubForm field Name to be totaled: Balance
frmForm footer textbox: txtTotal
Child16 Source Object: UnionQuery

UnionQuery SQL:
SELECT FundDate, FundID, FundName, Balance FROM Funds2009
UNION SELECT FundDate, FundID, FundName, Balance FROM Funds2008
UNION SELECT FundDate, FundID, FundName, Balance FROM Funds2007
UNION SELECT FundDate, FundID, FundName, Balance FROM Funds2006;


Here is what I'm trying to use in txtTotal's Control Source property
=Sum([Me]![Child16].[Balance])

You cannot sum *a control*; however you can sum a *field*.

If you don't want a subform for the union query - just the sum - you could
bypass the whole problem by using

=DSum("[Balance]", "[UnionQuery]")

as the control source of a textbox.