DSum([Field) in Subform where its current-records are stored with all subform records

DSum([Field) in Subform where its current-records are stored with all subform records

Post by phaddock » Wed, 26 Jan 2005 17:48:18

Being fairly inexperienced at Access 2000, i've been reading many posts
here for the last several days, and testing myself to find the best
approach to do the following in A2K:


I have created a MainTable (and related form), which has an associated
SubForm (popup) along with its underlying, separate Table.

The tables' relationship is one to many respectively. The primary key
for the MainTable is the ClientId, and the foreign key in the SubForm's

The MainForm shows a beginning balance, and allows users to select an
add-transaction button. If they do, then a data-entry-only version of
the SubForm appears, this works well. If prior transactions have been
entered since the original beginning balance, each of the preceding
transaction is posted and displays as a sequential record in the
continuous version of the SubForm. All Forms display by a single common

All new transactions for every account are stored in the SubForm's
many-Table (the beginning balance in the SubForm's footer).


My goal is to dispaly a current total in the footer of the SubForm
after every new transaction is entered, which includes the beginning
balance from the MainForm/Table.


I have tried DSum() but it totals the [Amount] of each record in the
entire domain. The records displayed in the continuous SubForm are a
subset, and this is the total I need. In other words, all transactions
are stored in the SubForm Table, but just the current record/ClientId
transactions are displayed and need totaled.

Also, iterating through the table, e.g., MoveNext, etc. is expensive in
time and overhead. But, I've tried this too with limited success.


Is there a way to select only part of the entire SubForm Table by
ClientId, so that the DSum() function just returns the sum for the
current Client displayed on the MainForm and its SubForm?

I did read where someone created a SnapShot Query on the continous
form's OpenForm event, and posted the total at that time. Then if
records in the continuous form were changed new calculations were
executed to adjust the total. Alas, no example, just verbage. Would
you use the RecordsetClone.args for this?

I've tried creating SQL statements in the design view and then placing
them in the VBA code, but if my statements are very long, and they are,
then Access belches at my crude implementations.


If anyone has been down this road before and garnered a successful
solution, I really do appreciate you sage advise or wisdom on this

DSum([Field) in Subform where its current-records are stored with all subform records

Post by Ed Robicha » Wed, 26 Jan 2005 23:27:05

One the better stated questions! Not exactly what you requested, but one of
the common solutions is to have an unbound control on the Main form that
uses DSum() to get the value from related records. If this appears just
below the amount control in your subform, users will understand.

You'd need to requery that control after editing/adding any record in the
subform. Use the AfterUpdate event of the subform to requery the unbound
"total amount" control.