Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by Niven View Post
    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)
    Regards
    John



  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



  4. #4
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by John Hutchison View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •