Access update text box problem 1

[MadCatmk2]

Hi All

I have a question regardin and access application i am helping to build. I have a subform with a number of text fields in the details section as follows:

Date Paid - Bound to field in payment table
Weeks Elapsed - shown as result of calculation (works fine)
Fee Due - shown as result of calculation (works fine)
Discount Given - Bound to field in payment table
Fee Due Less discount - shown as result of calculation (works fine)
Amount Due To Date - shown as result of calculation (works fine)
Amount Paid - Bound to field in payment table
Total Paid To Date - Bound to field in payment table
Balance - shown as result of calculation (works fine)

The problem i am having is with the [Total Paid To Date] field. I'd like to populate this with an updated total of the amount paid so far over a number of payments.
Also on the subform i have a field [Total Amount Paid]. This is a sum of all entries in the {Amount paid] field. At present there is a macro that is run which refreshes [Total Amount Paid] and then tries to set the [Total Paid To Date] field.

The setValue command in the Macro is set to this:
Item: [Forms]![Student]![Student on Courses subform]![Payment subform]![Total Paid to Date]
Expression: [Forms]![Payment subform]![Total Amount Paid]

I was hoping that this should assign the latest value of the [Total Amount Paid] to the latest [Total Paid To Date] in the details section. It doesn't however do that. When i change the expression to just a number it sets it no problem at all.

Hope this makes some sense.

Does anyone out there have any ideas how this could be done?

Thanks in advance.

 

[Frink]

Hallo,

IMHO, macros are horrible and should never be used for runtime code (with the possible exception of an AutoExec macro). But I digress.

You can use the DSum function to return the sum of values from a table.

- Frink

 

[MadCatmk2]

I don't like macros either but i'm sticking with how it was initially started and just trying to get this one problem sorted. How would i go about using the DSum function? i need to update the value in the field as a new record is added. WHere would the DSum be used?

Thanks for the reply.

 

[Frink]

Hallo,

On a form, the BeforeUpdate event is triggered immediately before the record is saved.
You could put me![Total Paid to Date]=Nz(DSum("[Amount Paid]","tblPayments","[Student]='" & Me![Student] & "'"),0)

Or something like that, However, you should consider not storing this value in the table, but calculating it (using DSum) every time, then it will always be correct.

- Frink

 

[MadCatmk2]

Thanks for that. The "not storing it in a table" idea sounds like a good one. If i went with that approach, would i just create a textbox and set the control source to a DSum formula?

Cheers

 

[Frink]

That's correct,

- Frink

 

[MadCatmk2]

OK. Sorry to ask yet another Dsum question (Brains not working this close to the holidays)

I'm using

I've got this so far:

Dsum("[Amount Paid]", "payment"

Presumably this will sum all [amount paid]'s in the table "payment". I now need to narrow down what is summed. I have a another three fields in the details (which will be hidden) They Are:

StudentSAN
CourseSAN
PaymentSAN

I want to sum all [amount paids]'s where studentSAN, courseSAN, are equal to there values on the form and PaymentSAN is less than its value on the form. How do i go about putting this into the DSum formula.

Thanks for all your help.

 

[Frink]

Hallo,

Did you read my example in my second post?

The third parameter of DSUM, which I had as "[Student]='" & Me![Student] & "'" is used to filter the DSUM total.
You probably want a third parameter something like:
"StudentSAN='" & me!StudentSAN & "' AND CourseSAN='" & me!CourseSAN & "' AND PaymentSAN < " & Me!PaymentSAN
Assuming StudentSAN and CourseSAN are strings and PaymentSAN is a number.

- Frink

 

[MadCatmk2]

Sorry. yes, i seen the example in your post. I got myself confused but i get the idea now.

Your help is much appreciated.

Thanks and Merry Christmas