473,372 Members | 1,003 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

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

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
1 4402
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: MX1 | last post by:
I have a query written in MS Access that has a few calculated fields. Is it possible to refer to that query in a form field. I'd like the form field to show the sum of one of the columns from the...
1
by: Alex | last post by:
Acc 97 Hi, I have the following in my query which works well if it is all set to numbers on table design. But what I want to do is where it states MC (short for machine name) use the actual...
4
by: John Baker | last post by:
Hi: I have a form, and wish to show on the form the current total for a single field on a table. I have set this up thus: =DSum(,!) in an unbound text field. These are correct field and table...
3
by: Mark Reed | last post by:
All, I have never used this function before and am not sure it what I need. Just to clarify, I have a report based on a query which has amoungst other field, wk, parea & packs_req. What I am...
4
by: John Baker | last post by:
Hi: At the bottom of a report, I wish to sum all the amounts that appear in a field called Discount$ that appears on the report. I am not doing this summing in the query that supports the report...
2
by: GD | last post by:
tblFoo FooId Name Number 1 One 1 2 Two 2 3 Three 3 The Following query produces the results I want, but it is not updatable. I need at least the...
3
by: beppe005 | last post by:
I would like to calculate a total for all the field with the same in a query. I don't know how to set the "criteria" for this dsum function, it should be something like = but it doesn't make...
4
by: john | last post by:
I have an unbound field in which I would like to show january 1 of the current year like this 01-01-2007. I tried this code but it doesn't work: =dateserial(Year(date()),1,1) Can someone help me...
4
by: Adam1331 | last post by:
I've been trying to get the DSum function to work but I've been running into some roadblocks. What I'm trying to do is to total up times in a timesheet report into different departments. The report...
3
by: majapa | last post by:
To start, here is my DSum expression: Develop: Nz(DSum(". & "] + . & "]","CSO","='Ability to Develop'")*-1,0) I actually have 4 of these, each one with a different option for . For more context,...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.