Using Arguments in a Procedure to do calculation

[murfeezlaw]

Hi all, i'm not the strongest of VBA programmers. I know there is an easier way to do this. I have a Loss Ratio field being calculated if the Loss field or Premium field is updated. I have 18 Loss Ratio fields that are going to be calculated so that means i would need 36 event procedures if i use an event procedure for each. I think i might be able to use my code w an Argument so i can call one procedure at each event procedure but i'm not sure how to do that. Any help would be very much appreciated.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub txtCurrentYearGLLosses_AfterUpdate()

If Not IsNull(txtCurrentYearGLPremium) And Not IsNull(txtCurrentYearGLLosses) Then

txtCurrentYearLostRatio = txtCurrentYearGLLosses / txtCurrentYearGLPremium

End If

End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub txtCurrentYearGLPremium_AfterUpdate()

If Not IsNull(txtCurrentYearGLPremium) And Not IsNull(txtCurrentYearGLLosses) Then

txtCurrentYearLostRatio = txtCurrentYearGLLosses / txtCurrentYearGLPremium

End If

End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[ssanfu]

Try this (untested):

Private Sub txtCurrentYearGLLosses_AfterUpdate()
   If Not IsNull(txtCurrentYearGLPremium) And Not IsNull(txtCurrentYearGLLosses) Then
      ' Call CalcRatio( PremiunFieldName, LossFieldName, "PutInControlName")
      Call CalcRatio(txtCurrentYearGLPremium, txtCurrentYearGLLosses, "txtCurrentYearLostRatio")
   End If
End Sub

Private Sub txtCurrentYearGLPremium_AfterUpdate()
   If Not IsNull(txtCurrentYearGLPremium) And Not IsNull(txtCurrentYearGLLosses) Then
      Call CalcRatio(txtCurrentYearGLPremium, txtCurrentYearGLLosses, "txtCurrentYearLostRatio")
   End If
End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub CalcRatio(pPremium As Single, pLosses As Single, pCtrl As String)
' if code behind form
  ' Me(pCtrl) = pLosses / pPremium

   ' if code is in a standard module or control is on a different form
      Forms!YourFormName.Controls(pCtrl) = pLosses / pPremium

End Sub

Change the blue "YourFormName" to your form(s) name(s).....

[John_G]

Hi -

One caveat to be aware of -

You are checking for null values, which is good, but if you accidently enter 0 as a value for txtCurrentYearGLPremium, you will get an error for dividing by zero. You will have to handle that possibility as well. (Why would 0 not be a valid value for txtCurrentYearGLPremium ? ).

John

[ssanfu]

Good point John... I should have thought of that!!

[murfeezlaw]

I should have thought of that. Theoretically you can't have a GL claim on a policy with no GL premium however i have seen it entered in some cases by mistake. Good call! Thanks guys. i haven't tried ssanfu's code yet but i probably will later today. Thank you! it looks good!

[murfeezlaw]

Try this (untested):

Private Sub txtCurrentYearGLLosses_AfterUpdate()
   If Not IsNull(txtCurrentYearGLPremium) And Not IsNull(txtCurrentYearGLLosses) Then
      ' Call CalcRatio( PremiunFieldName, LossFieldName, "PutInControlName")
      Call CalcRatio(txtCurrentYearGLPremium, txtCurrentYearGLLosses, "txtCurrentYearLostRatio")
   End If
End Sub

Private Sub txtCurrentYearGLPremium_AfterUpdate()
   If Not IsNull(txtCurrentYearGLPremium) And Not IsNull(txtCurrentYearGLLosses) Then
      Call CalcRatio(txtCurrentYearGLPremium, txtCurrentYearGLLosses, "txtCurrentYearLostRatio")
   End If
End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub CalcRatio(pPremium As Single, pLosses As Single, pCtrl As String)
' if code behind form
  ' Me(pCtrl) = pLosses / pPremium

   ' if code is in a standard module or control is on a different form
      Forms!YourFormName.Controls(pCtrl) = pLosses / pPremium

End Sub

Change the blue "YourFormName" to your form(s) name(s).....

Thank you, this is a big help. I know this is kind of a stupid question but now how do I pass the pCtrl varliable to the txtCurrentYearGLPremium_AfterUpdate procedure to set txtCurrentYearLostRatio equal to pCtrl? i tried using the Me(pCtrl) but it kept giving me a "Object doesn't support this property or method" so i tweaked it as below:

Private Sub txtCurrentYearGLPremium_AfterUpdate()
If Not IsNull(txtCurrentYearGLPremium) And Not IsNull(txtCurrentYearGLLosses) Then
Call CalcRatio(txtCurrentYearGLPremium, txtCurrentYearGLLosses, txtCurrentYearGLLossRatio)
End If

txtCurrentYearGLLossRatio = pCtrl


End Sub

Sub CalcRatio(pPremium As Single, pLosses As Single, pCtrl As Currency)
pCtrl = pLosses / pPremium
End Sub

[ssanfu]

I know this is kind of a stupid question but now how do I pass the pCtrl varliable to the txtCurrentYearGLPremium_AfterUpdate procedure to set txtCurrentYearLostRatio equal to pCtrl? i tried using the Me(pCtrl) but it kept giving me a "Object doesn't support this property or method" so i tweaked it as below:

Private Sub txtCurrentYearGLPremium_AfterUpdate()
If Not IsNull(txtCurrentYearGLPremium) And Not IsNull(txtCurrentYearGLLosses) Then
Call CalcRatio(txtCurrentYearGLPremium, txtCurrentYearGLLosses, txtCurrentYearGLLossRatio)
End If

txtCurrentYearGLLossRatio = pCtrl


End Sub

Not a stupid question.
I was trying to get too fancy.

You would use "Me(pCtrl) = pLosses / pPremium" if the "Sub CalcRatio" code was in the form module.
You would use "Forms!YourFormName.Controls(pCtrl) = pLosses / pPremium" if the "Sub CalcRatio" code was in a standard module.

You said

I have 18 Loss Ratio fields

so I was using "SUB CalcRatio" to do the calculation, then directly modify the control value.

Here is new code using a function. It also takes care of Nulls and Zeros.
** Note the order of the fields : Losses first, Premium second

Option Compare Database
Option Explicit  'always need this

'my test 
Private Sub Loss2_AfterUpdate()
   'control name
   Me.Ratio2 = CalcRatio(Me.Loss2, Me.Prem2)
End Sub

'my test
Private Sub Prem2_AfterUpdate()
   'control name
   Me.Ratio2 = CalcRatio(Me.Loss2, Me.Prem2)
End Sub


Private Sub txtCurrentYearGLLosses_AfterUpdate()
   'control name
   Me.txtCurrentYearLostRatio = CalcRatio(Me.txtCurrentYearGLLosses, Me.txtCurrentYearGLPremium)
End Sub


Private Sub txtCurrentYearGLPremium_AfterUpdate()
   'control name
   Me.txtCurrentYearLostRatio = CalcRatio(Me.txtCurrentYearGLLosses, Me.txtCurrentYearGLPremium)
End Sub


'---------------------------
Function CalcRatio(pLosses, pPremium) As Single
'return value is type Single
   Dim tmp As Single
   Dim vPrem As Single

   'take care of denominator is NULL or zero
   If pPremium = 0 Or IsNull(pPremium) Then
      tmp = 0
   Else
      vPrem = pPremium
      tmp = Nz(pLosses, 0) / vPrem
   End If

   'return value
   CalcRatio = tmp

End Function

[murfeezlaw]

Steve, WORKED LIKE A CHARM! Thank you so much for your help on this. I learned quite a bit about passing arguments also. Thank you!

[ssanfu]

You're welcome