DSum, Function Methods - Need Assistance

[Dalan]

I have tried both methods of using DSum and creating a Function to
address summing some number columns, but to no avail. Since this has
been a popular topic over the years, I'm sure I'll receive some
guidance. This is related to Access 97 and involves my redoing a
miscellaneous purchase and sales form.

A main form (frmMain) is used that contains summary information of the
item. I incorporated two continuous subforms (sfrmPurchases) and
(sfrmSales). Of course, I also created two new tables for the subforms
(tblPurchases) and (tblSales). And also created three queries
(qryPurSales) and (qryPurchases), (qrySales) - the latter two use the
Sum function to group by ID and sum by the columns. The relationships
are the main form table as one to the two subform tables as many.

Everything seems to work okay, except that I'm having difficulty
sorting out the DSum expressions to use to provide some of the column
totals. Additionally, I have summary section at the top on the main
form showing the grand total results of the subforms along with more
calculations.

The three column totals that I having difficulty with all probably
require a similar expression. It is the sfrmSales TotalSales, Discount
and NetSales columns that do not total. The totals across are okay,
but the grand total at the bottom of each is not. As a side note, one
of the expressions I have used for the Purchases to be summarized in
the main form section which works is:
=DSum("[Purchases]","tblPurchases", "[IDPur] = " & [IDMain])

I also tried using a Function module to open a SQL recordset, but
could not get it to work either. I have substituted many variations of
(IDSales), (tblSales), (qryPurSales) and (qrySales) in my expressions.

Any assistance will be greatly appreciated. Thanks.

[MacDermott]

Say you have a subform named sfm1; its recordset includes a field named ABC
which you want to total and show on the main form.
Put a textbox on the footer of the subform (the textbox, or even the entire
footer can be set to visible=false).
Let's call it TotalABC; set its ControlSource like this:
=Sum(ABC)
Now put a textbox named MainABC on your main form; set its ControlSource
like this:
=sfm1.Form.TotalABC

HTH
- Turtle

"Dalan" <ot***@safe-mail.net> wrote in message
news:50**************************@posting.google.c om...

I have tried both methods of using DSum and creating a Function to
address summing some number columns, but to no avail. Since this has
been a popular topic over the years, I'm sure I'll receive some
guidance. This is related to Access 97 and involves my redoing a
miscellaneous purchase and sales form.

A main form (frmMain) is used that contains summary information of the
item. I incorporated two continuous subforms (sfrmPurchases) and
(sfrmSales). Of course, I also created two new tables for the subforms
(tblPurchases) and (tblSales). And also created three queries
(qryPurSales) and (qryPurchases), (qrySales) - the latter two use the
Sum function to group by ID and sum by the columns. The relationships
are the main form table as one to the two subform tables as many.

Everything seems to work okay, except that I'm having difficulty
sorting out the DSum expressions to use to provide some of the column
totals. Additionally, I have summary section at the top on the main
form showing the grand total results of the subforms along with more
calculations.

The three column totals that I having difficulty with all probably
require a similar expression. It is the sfrmSales TotalSales, Discount
and NetSales columns that do not total. The totals across are okay,
but the grand total at the bottom of each is not. As a side note, one
of the expressions I have used for the Purchases to be summarized in
the main form section which works is:
=DSum("[Purchases]","tblPurchases", "[IDPur] = " & [IDMain])

I also tried using a Function module to open a SQL recordset, but
could not get it to work either. I have substituted many variations of
(IDSales), (tblSales), (qryPurSales) and (qrySales) in my expressions.

Any assistance will be greatly appreciated. Thanks.