Problems with DSum or Running Sum on a Subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to run a subform as a datasheet with a DSum field that produces a
running sum. I have not been able to get the DSum field to work when the
subform is embedded in the main form. The DSum field works correctly,
however, when the subform is called from the main form by a command button.
Is there any way to embed the subform and retain the functionality of the
DSum field? The details of my project are given below. Thanks for reading.

I have a main form "Work" based on the table "Accounts" with the field
"AccountID". (It contains other fields not pertinent to this question).
I have a subform "Work Subform" based on a query "TransQuery" which in turn
is based on a table "Transactions".

The query and subform have the following fields: DepositAmount,
WithdrawalAmount, TransactionID and AccountID.

The query has the parameter for AccountID of "Forms![Work]![AccountID]"

The subform also contains a calculated field, Balance, with the value
=DSum("[DepositAmount]-[WithdrawalAmount]","TransQuery","[TransactionID]
<=Forms![Work Subform]![TransactionID]")

Again, when I open the main form with the subform embedded in it, the data
in the subform is present and corresponds to the data in the form (through
the AccountID field in the form, subform, and query. The subform's behavior
does not change if the Master/Child fields are linked, AccountID to
AccountID, or not). The field with DSum in it returns #Error. I have a
command button on the main form that opens the subform as a separate form.
When this is used to open the subform as a separate form, the DSum field
works correctly.

Is there anything I can do? Or should I do something entirely different to
get the same results? BTW, I have been very pleased with the answers in this
discussion group on how to create a running sum using DSum.
 
Try
=DSum("[DepositAmount]-[WithdrawalAmount]","TransQuery","[TransactionID]

<= " & Forms![Work Subform].Form![TransactionID])

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I am trying to run a subform as a datasheet with a DSum field that produces a
running sum. I have not been able to get the DSum field to work when the
subform is embedded in the main form. The DSum field works correctly,
however, when the subform is called from the main form by a command button.
Is there any way to embed the subform and retain the functionality of the
DSum field? The details of my project are given below. Thanks for reading.

I have a main form "Work" based on the table "Accounts" with the field
"AccountID". (It contains other fields not pertinent to this question).
I have a subform "Work Subform" based on a query "TransQuery" which in turn
is based on a table "Transactions".

The query and subform have the following fields: DepositAmount,
WithdrawalAmount, TransactionID and AccountID.

The query has the parameter for AccountID of "Forms![Work]![AccountID]"

The subform also contains a calculated field, Balance, with the value
=DSum("[DepositAmount]-[WithdrawalAmount]","TransQuery","[TransactionID]
Click to expand...
<=Forms![Work Subform]![TransactionID]")

Again, when I open the main form with the subform embedded in it, the data
in the subform is present and corresponds to the data in the form (through
the AccountID field in the form, subform, and query. The subform's behavior
does not change if the Master/Child fields are linked, AccountID to
AccountID, or not). The field with DSum in it returns #Error. I have a
command button on the main form that opens the subform as a separate form.
When this is used to open the subform as a separate form, the DSum field
works correctly.

Is there anything I can do? Or should I do something entirely different to
get the same results? BTW, I have been very pleased with the answers in this
discussion group on how to create a running sum using DSum.
.
Click to expand...
 
Drhalter

I suggest trying it like this...

=DSum("[DepositAmount]-[WithdrawalAmount]","TransQuery","[TransactionID]
<=" & [TransactionID])
 
Just to add a note of explanation, a reference to [Forms]![Work
Subform]![TransactionID] will not compute, because the [Work Subform]
form is not actually open... being displayed through a subform control
on another form does not count as "open".
 
Thanks Steve,

I tried that out and it worked like a charm. Can't say why I didn't think
to try it before! Oh well, thanks again.

Dave

Steve Schapel said:
Drhalter

I suggest trying it like this...

=DSum("[DepositAmount]-[WithdrawalAmount]","TransQuery","[TransactionID]
<=" & [TransactionID])

--
Steve Schapel, Microsoft Access MVP
I am trying to run a subform as a datasheet with a DSum field that produces a
running sum. I have not been able to get the DSum field to work when the
subform is embedded in the main form. The DSum field works correctly,
however, when the subform is called from the main form by a command button.
Is there any way to embed the subform and retain the functionality of the
DSum field? The details of my project are given below. Thanks for reading.

I have a main form "Work" based on the table "Accounts" with the field
"AccountID". (It contains other fields not pertinent to this question).
I have a subform "Work Subform" based on a query "TransQuery" which in turn
is based on a table "Transactions".

The query and subform have the following fields: DepositAmount,
WithdrawalAmount, TransactionID and AccountID.

The query has the parameter for AccountID of "Forms![Work]![AccountID]"

The subform also contains a calculated field, Balance, with the value
=DSum("[DepositAmount]-[WithdrawalAmount]","TransQuery","[TransactionID]
<=Forms![Work Subform]![TransactionID]")

Again, when I open the main form with the subform embedded in it, the data
in the subform is present and corresponds to the data in the form (through
the AccountID field in the form, subform, and query. The subform's behavior
does not change if the Master/Child fields are linked, AccountID to
AccountID, or not). The field with DSum in it returns #Error. I have a
command button on the main form that opens the subform as a separate form.
When this is used to open the subform as a separate form, the DSum field
works correctly.

Is there anything I can do? Or should I do something entirely different to
get the same results? BTW, I have been very pleased with the answers in this
discussion group on how to create a running sum using DSum.
Click to expand...
Click to expand...
 
Back
Top