running sum on subform

[Guest]

I have a main form that has a subform control on it. One of the subforms
that is used has a tab control with another subform on it. In this subform I
would like to have a field in the footer that provides a running sum. I
placed the field with the following expression;

=DSum("[ExpenseAmount]","tblExpense","[ExpenseID] <=
Forms![sfrmExpense]![ExpenseID]")

When I run the app I get the error word in the field. If I run just the
form itself, the running sum field works like it should.
Can anyone assist on this? Thanks for responding.
*** John

 

[kingston via AccessMonster.com]

Assuming ExpenseID is a number:

=DSum("[ExpenseAmount]","tblExpense","[ExpenseID]<=" & Forms![sfrmExpense]!
[ExpenseID])

I have a main form that has a subform control on it. One of the subforms
that is used has a tab control with another subform on it. In this subform I
would like to have a field in the footer that provides a running sum. I
placed the field with the following expression;

=DSum("[ExpenseAmount]","tblExpense","[ExpenseID] <=
Forms![sfrmExpense]![ExpenseID]")

When I run the app I get the error word in the field. If I run just the
form itself, the running sum field works like it should.
Can anyone assist on this? Thanks for responding.
*** John

 

[Guest]

Thanks. Now I get the Name? error notice in the field. I am going to try
and give a rundown starting with the mainform.

mainform
subform control (this control holds all of the subforms when called thru a
menu)
subform (inside the above subform control)
tab control
subform on the tab control

The way this is set up might sound odd but there aren't multiple subforms
needing to be loaded when the mainform opens. Only one subform is loaded and
the others are called thru a menu and are opened/placed in the subform
control.

Hope this helps out more. What you provided works if I open the subform
only and not thru any other method.

*** John

Assuming ExpenseID is a number:

=DSum("[ExpenseAmount]","tblExpense","[ExpenseID]<=" & Forms![sfrmExpense]!
[ExpenseID])

I have a main form that has a subform control on it. One of the subforms
that is used has a tab control with another subform on it. In this subform I
would like to have a field in the footer that provides a running sum. I
placed the field with the following expression;

=DSum("[ExpenseAmount]","tblExpense","[ExpenseID] <=
Forms![sfrmExpense]![ExpenseID]")

When I run the app I get the error word in the field. If I run just the
form itself, the running sum field works like it should.
Can anyone assist on this? Thanks for responding.
*** John

 

[Marshall Barton]

I have a main form that has a subform control on it. One of the subforms
that is used has a tab control with another subform on it. In this subform I
would like to have a field in the footer that provides a running sum. I
placed the field with the following expression;

=DSum("[ExpenseAmount]","tblExpense","[ExpenseID] <=
Forms![sfrmExpense]![ExpenseID]")

When I run the app I get the error word in the field. If I run just the
form itself, the running sum field works like it should.

A subform is not open in its own right. You must go through
the subform control that contains the (sub)form. In this
case, it appears that the ExpenseID text box is on the same
subform, so you can get to it directly:

=DSum("ExpenseAmount","tblExpense","ExpenseID <= " &
ExpenseID)


If you find this too slow to be useful, then I suggest that
you seriously reconsider the idea of having a running sum on
the (sub)form.

If you must display the running sum, it might(?) be faster
to calculate it in the (sub)form's record source query (by
using a subquery)

 

[Guest]

Marsh, thanks. Your response was right on as I didn't need the extra
language in there. This method should be fast enough.
*** John

I have a main form that has a subform control on it. One of the subforms
that is used has a tab control with another subform on it. In this subform I
would like to have a field in the footer that provides a running sum. I
placed the field with the following expression;

=DSum("[ExpenseAmount]","tblExpense","[ExpenseID] <=
Forms![sfrmExpense]![ExpenseID]")

When I run the app I get the error word in the field. If I run just the
form itself, the running sum field works like it should.

A subform is not open in its own right. You must go through
the subform control that contains the (sub)form. In this
case, it appears that the ExpenseID text box is on the same
subform, so you can get to it directly:

=DSum("ExpenseAmount","tblExpense","ExpenseID <= " &
ExpenseID)


If you find this too slow to be useful, then I suggest that
you seriously reconsider the idea of having a running sum on
the (sub)form.

If you must display the running sum, it might(?) be faster
to calculate it in the (sub)form's record source query (by
using a subquery)

 

[Guest]

Marsh, general question, How would what you have here work if there were 2
fields (same table/same subform) that you wanted the running sum on, fieldA
subtracts from fieldB? Now that this works, I suspect the users will want it

I have a main form that has a subform control on it. One of the subforms
that is used has a tab control with another subform on it. In this subform I
would like to have a field in the footer that provides a running sum. I
placed the field with the following expression;

=DSum("[ExpenseAmount]","tblExpense","[ExpenseID] <=
Forms![sfrmExpense]![ExpenseID]")

When I run the app I get the error word in the field. If I run just the
form itself, the running sum field works like it should.

A subform is not open in its own right. You must go through
the subform control that contains the (sub)form. In this
case, it appears that the ExpenseID text box is on the same
subform, so you can get to it directly:

=DSum("ExpenseAmount","tblExpense","ExpenseID <= " &
ExpenseID)


If you find this too slow to be useful, then I suggest that
you seriously reconsider the idea of having a running sum on
the (sub)form.

If you must display the running sum, it might(?) be faster
to calculate it in the (sub)form's record source query (by
using a subquery)

 

[Marshall Barton]

I don't understand what calculation you want to do. An
example might help clarify it.

Maybe you want to sum the difference:
=DSum("fieldB - fieldA","tblExpense","ExpenseID <= " &
ExpenseID)

or do you want to have two separate running sums using two
text boxes with slightly different DSum expressions?

Please don't get carried away with this kind of calculation.
As your table gets larger, this kind of calculation can
become abysmally slow.
--
Marsh
MVP [MS Access]

Marsh, general question, How would what you have here work if there were 2
fields (same table/same subform) that you wanted the running sum on, fieldA
subtracts from fieldB? Now that this works, I suspect the users will want it
on several other subforms as well.

I have a main form that has a subform control on it. One of the subforms
that is used has a tab control with another subform on it. In this subform I
would like to have a field in the footer that provides a running sum. I
placed the field with the following expression;

=DSum("[ExpenseAmount]","tblExpense","[ExpenseID] <=
Forms![sfrmExpense]![ExpenseID]")

When I run the app I get the error word in the field. If I run just the
form itself, the running sum field works like it should.

A subform is not open in its own right. You must go through
the subform control that contains the (sub)form. In this
case, it appears that the ExpenseID text box is on the same
subform, so you can get to it directly:

=DSum("ExpenseAmount","tblExpense","ExpenseID <= " &
ExpenseID)


If you find this too slow to be useful, then I suggest that
you seriously reconsider the idea of having a running sum on
the (sub)form.

If you must display the running sum, it might(?) be faster
to calculate it in the (sub)form's record source query (by
using a subquery)

 

[Guest]

Unfortunately, an email came thru that indicated that users would like to
have a field that will give a running sum the same as what is done in the
Expense part (the original issue of this posting).

Need to review the email below to see where the subform is located. It is a
continuous type. There are 2 fields per record that shows incoming and
outgoing funds. There is an overall total field in the footer but the users
would like to have the availability to select any record and see what the
running sum (field in footer) would be to that point. This is similar to the
Expense issue in this posting with the exception of 2 fields making a
calculation. So Field A is subtracted from Field B.

I checked to see about the volume of records in the table. For now there
are several hundred with about 15-20 new records added each day. For now
there shouldn't be a problem. By the time it does, a better method will be
figured out. But for now, need a "quick fix" to pease the masses.

Any insight to this is welcomed.

*** John

I don't understand what calculation you want to do. An
example might help clarify it.

Maybe you want to sum the difference:
=DSum("fieldB - fieldA","tblExpense","ExpenseID <= " &
ExpenseID)

or do you want to have two separate running sums using two
text boxes with slightly different DSum expressions?

Please don't get carried away with this kind of calculation.
As your table gets larger, this kind of calculation can
become abysmally slow.
--
Marsh
MVP [MS Access]

Marsh, general question, How would what you have here work if there were 2
fields (same table/same subform) that you wanted the running sum on, fieldA
subtracts from fieldB? Now that this works, I suspect the users will want it
on several other subforms as well.

JohnE wrote:

I have a main form that has a subform control on it. One of the subforms
that is used has a tab control with another subform on it. In this subform I
would like to have a field in the footer that provides a running sum. I
placed the field with the following expression;

=DSum("[ExpenseAmount]","tblExpense","[ExpenseID] <=
Forms![sfrmExpense]![ExpenseID]")

When I run the app I get the error word in the field. If I run just the
form itself, the running sum field works like it should.

A subform is not open in its own right. You must go through
the subform control that contains the (sub)form. In this
case, it appears that the ExpenseID text box is on the same
subform, so you can get to it directly:

=DSum("ExpenseAmount","tblExpense","ExpenseID <= " &
ExpenseID)


If you find this too slow to be useful, then I suggest that
you seriously reconsider the idea of having a running sum on
the (sub)form.

If you must display the running sum, it might(?) be faster
to calculate it in the (sub)form's record source query (by
using a subquery)

 

[Guest]

Never mind. Got it to work out. Thanks.

Unfortunately, an email came thru that indicated that users would like to
have a field that will give a running sum the same as what is done in the
Expense part (the original issue of this posting).

Need to review the email below to see where the subform is located. It is a
continuous type. There are 2 fields per record that shows incoming and
outgoing funds. There is an overall total field in the footer but the users
would like to have the availability to select any record and see what the
running sum (field in footer) would be to that point. This is similar to the
Expense issue in this posting with the exception of 2 fields making a
calculation. So Field A is subtracted from Field B.

I checked to see about the volume of records in the table. For now there
are several hundred with about 15-20 new records added each day. For now
there shouldn't be a problem. By the time it does, a better method will be
figured out. But for now, need a "quick fix" to pease the masses.

Any insight to this is welcomed.

*** John

I don't understand what calculation you want to do. An
example might help clarify it.

Maybe you want to sum the difference:
=DSum("fieldB - fieldA","tblExpense","ExpenseID <= " &
ExpenseID)

or do you want to have two separate running sums using two
text boxes with slightly different DSum expressions?

Please don't get carried away with this kind of calculation.
As your table gets larger, this kind of calculation can
become abysmally slow.
--
Marsh
MVP [MS Access]

Marsh, general question, How would what you have here work if there were 2
fields (same table/same subform) that you wanted the running sum on, fieldA
subtracts from fieldB? Now that this works, I suspect the users will want it
on several other subforms as well.


JohnE wrote:

I have a main form that has a subform control on it. One of the subforms
that is used has a tab control with another subform on it. In this subform I
would like to have a field in the footer that provides a running sum. I
placed the field with the following expression;

=DSum("[ExpenseAmount]","tblExpense","[ExpenseID] <=
Forms![sfrmExpense]![ExpenseID]")

When I run the app I get the error word in the field. If I run just the
form itself, the running sum field works like it should.


:
A subform is not open in its own right. You must go through
the subform control that contains the (sub)form. In this
case, it appears that the ExpenseID text box is on the same
subform, so you can get to it directly:

=DSum("ExpenseAmount","tblExpense","ExpenseID <= " &
ExpenseID)


If you find this too slow to be useful, then I suggest that
you seriously reconsider the idea of having a running sum on
the (sub)form.

If you must display the running sum, it might(?) be faster
to calculate it in the (sub)form's record source query (by
using a subquery)