How to sum all payments in database and show on form

[adna]

How to sum all payments in database and show on form?
I posted a question yesterday about my form, and have a new question about
the same form.

I have a form frmVendor with a subform frmPayment. They are linked to
tables tblVendor and tblPayments (which are related by VendorID).

The form frmVendor shows one vendor at a time, with subform showing only
payments made to that vendor.

I want to put a calculated field in the main form (frmVendor) header showing
the total of all payments in the tblPayments (not just the total for the
vendor currently being viewed.)

Reason being, Company has budget of 20,000 to spend and wants to show
running total at the top of the main form so they can see how close they are
getting to 20,000.

 

[adna]

Wanted to add that I've tried the following and just get #Error.
I added an unbound txt box and set it to =Sum([Payments])
It doesn't work .

 

[Lance]

Make a query which sums your payments. Then as the data source for your
textbox use something like:

=DLookUp("[SUMFIELD]","SUM_QUERY")

 

[C Hayes]

this may seem strange and not the best way but this is what I tried and it
seems to work:

add a text box to your header call it "txtTotal"

select the properties of the form and find the event "on current"

add the following code

Private Sub Form_Current()
Dim dblTotal As Double
Dim db As DAO.Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Sum(Payments.PaymentAmount) AS
SumOfPaymentAmount FROM Payments;")
dblTotal = rs!sumofpaymentamount
Me.txtTotal = dblTotal
End Sub

This won't change the total once you add a payment but it will update it
every time you open the form or change to a new record. There would need to
be some more code behind some other events to cause it to update more often.

 

[adna]

I found a solution by using the DSum function.
DSum("[Payments]", "tblPayments")

Wanted to add that I've tried the following and just get #Error.
I added an unbound txt box and set it to =Sum([Payments])
It doesn't work .

How to sum all payments in database and show on form?
I posted a question yesterday about my form, and have a new question about
the same form.

I have a form frmVendor with a subform frmPayment. They are linked to
tables tblVendor and tblPayments (which are related by VendorID).

The form frmVendor shows one vendor at a time, with subform showing only
payments made to that vendor.

I want to put a calculated field in the main form (frmVendor) header showing
the total of all payments in the tblPayments (not just the total for the
vendor currently being viewed.)

Reason being, Company has budget of 20,000 to spend and wants to show
running total at the top of the main form so they can see how close they are
getting to 20,000.