By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,663 Members | 1,844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,663 IT Pros & Developers. It's quick & easy.

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

P: n/a
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:

SET UP:

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
Table.

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
ClientId.

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

GOAL:

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.

ATTEMPTS:

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.

POSSIBILITIES:

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.

REQUEST:

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

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.
-Ed

<ph*******@sbcglobal.net> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
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:

SET UP:

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
Table.

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
ClientId.

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

GOAL:

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.

ATTEMPTS:

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.

POSSIBILITIES:

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.

REQUEST:

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

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.