Calculation problem with subforms

[danikuper]

I have a main form with two subforms:

- Tracking
- Payments

The main form contains customer info (ID, Name, address) while the payments subform is a datasheet with fields "payment_amount" and "payment_date"; the tracking subform has "total_due" and "total_paid".

I want to calculate the "total_paid" based on the payments that come in. Each new record in the Payments subform would update the "total_paid" field for that customer.

My initial idea was to have something on the "after update" event of the Payments subform that would go over each record, add the payments and update the "total_paid" field on the other subform with that value.

Is this possible? How do I do that? Other ideas are very welcome!!!

Thanks!

P.S. using Access 2000

 

[pono1]

You can use the DSUM function, I would think, putting something like the following into an unbound textbox's control source property:

=DSUM("payment_amount","TblPayments","CusID = " & Forms!MainForm!txtCusID)

'Assuming your subform is linked to your main form using
'the primary key in the customer table.

'Assuming the primary key is called CusID in customer table.
'txtCusID = control name

'TblPayments = Table bound to subform

If you're extremely speed sensitive, you can also do the same thing writing code using DAO or ADO.

Regards,
Tim

 

[danikuper]

Hi,

I tried what you suggested but it shows #Error in the field where I have the Dsum expression.

The expression I'm using is:

=DSum("paid_amt","TblPayments","Custcode = " & [Forms]![frmCustomer_Main]![Custcode])

Maybe I'm doing something wrong?

 

[pono1]

The good thing is that the syntax looks OK. The bad thing is that the syntax looks OK.

Are you sure you have field names, the table name, and the control name correctly spelled?

That is, re-verify that there is a table named TblPayments in your DB and that there are two fields in the table named paid_amt and Custcode and, too, that there is a control on your main form named CustCode.

Lastly, verify that the CustCode field in TblPayments is a data type Number, not Text.

That's about everything I can think of...

Regards,
Tim

 

[danikuper]

Humm... does DSUM work if the data type is currency?

 

[Rich]

=Sum([AmountPaid]) in an unbound textbox in the subform footer will do what you want without any code. You shouldn't store the TotalPaid either.