Totaling A Subform

headintheclouds

Registered User.
Local time
Today, 23:18
Joined
May 2, 2005
Messages
24
Hi there hope you can help :)

I have created a form with a subform and has the following information displayed:

(Form)
Tenant ID Mobile Telephone Number
Title Credit Reference
Surname
First Name

(Subform)
Date Due Amount Paid On


(Form) Total Of Arrears -

I need text box at the bottom of the form to caculate each tenants arrears.
To show if the tennant has any arrears there will be no date shown in the [Paid On] box (If there is a date in this box it means the tenant has paid and therefore no arrears) so the [Amount] for that tenant owes will be totaled up in the box at the bottom of the page [Total of Arrears]. Also a tenant might miss a payment on one week and a pay another rent payment on time. Please could you show how to do this in access and not by VB code.

I've attached my database - well thanks for looking and hope you can help

HeadintheClouds
 

Attachments

I guess maybe I don't understand your question. The textbox at the bottom of the form seems to calculate the figures correctly. What exactly is the problem? Please explain in further detail - precise examples, etc.

One thing I notice is that you are storing a calculated value. In your subform's On Current event you are storing Amount (a number data type) in Arrears Due (converting to a currency format) when the Paid On field is null. Doing this is not necessary nor recommended as you can always determine if there is arrears due based on the other 2 fields. The amount field should be a currency format to begin with.
 
Last edited:
Hi mate

My fault I should have explained myself better :o, To get [Total Arrears] to work in the form I had to add an extra column in tblrents but is there a way of working out the arrears without adding an arrears field, so in the subform I have:

date due
amount
paid on (if null means arrears)

so is there some kind statment (maybe a iif statement) that I can put in the [paid on] field that will automatically work out total arrears if new records are added for each tenant.
 
You can use the On Current event of your tenant details form (not the subform) and use DSum to calculate the arrears due:

Me.txtArrearsDue = Nz(DSum("[Amount]", "[tblRent]", "[Tenant ID] = " & Me.[Tenant ID] & " And IsDate(Nz([Paid On])) = False"), 0)

The problem is that currently this does not sum correctly because the amount field is a Long Integer data type with a currency format. If you change the field data type to currency the figures will change so be careful if you have sensitive data.
 

Users who are viewing this thread

Back
Top Bottom