function not working properly

[ninja_imp]

HI

I finally got a function to sort of work but cant understand why it is doing certain things

the idea is that it calculates the total of Pubprice and then inserts it into the TestPayment field.

Now the function only works on the first customer on the comp screen but when you change to another customer it doesnt make any changes. I've tried putting it in the OnCurrent event but that just kicks up an error!

My code is:

Public Function MyPaymentTotal() As Double

On Error GoTo MyPaymentTotal_Err
Dim DetailRst As DAO.Recordset
Dim CurDB As DAO.Database
Dim PaymentTotal As Currency

Set CurDB = CurrentDb
PaymentTotal = 0

' open read only recordset to total up all amounts
Set DetailRst = CurDB.OpenRecordset("Select Sum(Tbl_PublicationNew.PubPrice)As DailyTotal FROM [Tbl_PublicationNew] WHERE [Tbl_PublicationNew]![CustRef] = " & [Forms]![Frm_Customer]![CustRef])



If Not DetailRst.EOF Then
PaymentTotal = Nz(DetailRst!DailyTotal, 0)
End If
DetailRst.Close

Forms![Frm_Customer]![testPayment] = PaymentTotal

MyPaymentTotal_Exit:
Exit Function

MyPaymentTotal_Err:
MsgBox Err.Description
Resume MyPaymentTotal_Exit

End Function


Any help at all would be greatly appreciated

Kind Regards

Simon

 

[WayneRyan]

Simon,

Put your public function in a module (on the Modules Tab).

Add an argument for the [CustRef] field.

In the query that feeds your form/report add a new field:

TestPayment: MyPaymentTotal([CustRef])

Then you can reference TestPayment and it will have the appropriate value in it.

As an alternative, you can use the OnCurrent event and use the DSum function:

Me.TestPayment = Nz(DSum("[PubPrice]", "Tbl_PublicationNew", "[CustRef] = " & Me.CustRef), 0)

hth,
Wayne

 

[ninja_imp]

I seem to have got it working now thanks but one more thing, when a value is entered or updated the calculation doesnt update itself unles you close and re open the form or move forward 1 and then back again! Any ideas how i can get it to do it after a value has been entered?

Cheers

 

[WayneRyan]

Simon,

If you want to handle cases where the user updates the value on the
form you can't count on just the query. The query servers its purpose.
You need to add the following to the BeforeUpdate event of any field
which affects the calculation.

Me.TestPayment = Nz(DSum("[PubPrice]", "Tbl_PublicationNew", "[CustRef] = " & Me.CustRef), 0)

Wayne