using a DSum() function in a form footer

  • Thread starter Thread starter Paul James
  • Start date Start date
P

Paul James

I'm trying to add up the values of all records in a total field that have
been paid, and I'd like to display this sum of paid totals in the form
footer. I've been trying to accomplish this with DSum which I know how to
use when I'm looking up values in a table. I've tried specifying the
arguments in DSum to total the specified values in the form by replacing the
field and table names with every possible articulation of the form and
control specs I could think of, such as

formcontrolname
me!formcontrolname
forms!formname!formcontrolname

but again, I can't get past the #Error message.

Can anyone help me with this? Please assume the object names are:

txtTotal - the name of the form control I'm trying to sum up with DSum
frmReceiptSub - the name of the form that contains txtTotal
chkPaid - the name of the "Paid" checkbox control in frmReceipt
txtSumTotal - the name of the form footer control that I'm putting the
DSum() function into.

If this is relevant, the fields in the query underlying frmReceipt are named
"Total" and "Paid."

I suppose I could obtain the values for for txtSumTotal from the query
recordset, but what I'm trying to do with this form, which is a subform of
another form, is to instantaly display the Total value of all paid invoices
as soon as the Paid field of another Invoice is checked. I figured that if
I'm doing all the calculations in the form, I could bypass worrying about
refreshing and requerying the underlying table and query. Hence it will be
faster and simplify the VBA code behind my forms.

Can anyone tell me how to write the DSum() function?

Thanks in advance,

Paul
 
DSum requires a table or saved query as the domain reference, and it
does not use form controls - unless you reference them for their
values. The control source of txtSumTotal should be something like...

= DSum("Total", "tblTotals", "Paid = True And InvcNum = " & Me.InvcNum
)

Watch out for word wrap and of course replace with the your correct
field names.

Note that you will have to requery txtSumTotal.

- Jim
 
Thanks, Jim. You've shed some light on using the DSum() function (and thus
other domain aggregate functions) for me.

In applying your solution, I realized that I had ommitted an important fact
from my original post. The only records that are displayed in this
(sub)form are the unpaid invoices. Thus, if the query is updated after a
record is marked paid, the record disappears from the subform.

So you made me realize I need to solve my problem with a different approach.
Here's what I did:

I created a Static variable in the After Update event of the Paid checkbox
which adds the txtTotal value to the variable whenever the record is checked
as paid, and subtracts the txtTotal value if the record is unchecked. It
then assigns the value of that variable to the control on the main form,
which displays the sum of the invoices that were checked as paid. I also
reset the value of that static variable to zero in the form's After Update
event.

It works great, and my users will have a nifty little calculator on their
form.

So thanks again, Jim, for giving me the information I needed to get on the
right track.

Paul
 
Jim - I also meant to thank you for the structure of the DSum() function in
an example such as I described, and for explaining why I couldn't get it to
work by using form controls in place of the table or query parameters.

Paul
 
You're welcome Paul, I'm glad that I could contribute to the
resolution of your problem.

- Jim
 
Back
Top