User defined Function performance issue

Topic

New Reply

Hi there!

I have recently created a function that picks out a credit card charge rate and then applies this against a credit card payment in a textbox on a form.

I.e. txtCreditCardCharge: Round([txtPreBookedCard]*CardCharge(“ChargeRate”),2)

What puzzles me is when I open the respective form there is a noticeable delay in how long it takes the txtCreditCardCharge field to display the value in its field. Other fields on the form are drawn direct from a table called payments.

Can anyone advise me as to why this is the case? Could it be the code in the function? I’ve posted it below. (incidentally there is currently only one record in tblCardDetails)

Cheers,

Niven

Public Function CardCharge(ByVal ChargeRate)

Dim rsChargeRate As Recordset
Dim strSQL2 As String
Dim strChargeRate As Double

strSQL2 = “SELECT ChargeRate from tblCardDetails where CardType = ‘CC'”

Set rsChargeRate = CurrentDb.OpenRecordset(strSQL2)

rsChargeRate.MoveFirst

strChargeRate = rsChargeRate(“ChargeRate”)

rsChargeRate.Close
Set rsChargeRate = Nothing

CardCharge = strChargeRate

End Function

Reply | Quote

Replies

Public Function CardCharge(ByVal ChargeRate)

Dim rsChargeRate As Recordset
Dim strSQL2 As String
Dim strChargeRate As Double

strSQL2 = “SELECT ChargeRate from tblCardDetails where CardType = ‘CC'”

Set rsChargeRate = CurrentDb.OpenRecordset(strSQL2)

rsChargeRate.MoveFirst

strChargeRate = rsChargeRate(“ChargeRate”)

rsChargeRate.Close
Set rsChargeRate = Nothing

CardCharge = strChargeRate

End Function

Is it any quicker if you use this function

Code:

Public Function fnCardCharge() as double

 Dim dbChargeRate As Double
 dbChargeRate =dlookup("ChargeRate",tblCardDetails","CardType =" & chr(34) & CC & chr(34)")
 fnCardCharge = dbChargeRate
End Function

A Dlookup returns the first matching value. So if there is only that will be it.

Then

txtCreditCardCharge: Round([txtPreBookedCard]*fnCardCharge(),2)

Reply | Quote

The quickest option is always to avoid using custom functions if you can.

If you had a card type field in the Payments table, you should be able to join on this field to tblCardDetails, and so avoid using a function completely.

Reply | Quote

The quickest option is always to avoid using custom functions if you can.

If you had a card type field in the Payments table, you should be able to join on this field to tblCardDetails, and so avoid using a function completely.

Hi John!

Many thanks your reply. I tried your DLookup suggestion and the performance issue is the same.

I’m going to look at joining the table as an alternative means.

Cheers,

Niven

Reply | Quote