Using Arguments in a Procedure

murfeezlaw

New member
Local time
Today, 05:04
Joined
Dec 5, 2006
Messages
7
Hi all, i'm not the strongest of VBA programmers. I know there has to be 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 36 event procedures. I know that's a waste of code. I think i might be able to use my code w an Argument 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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
One better way is to use Select Case Statement. Where you can use Cases instead of so many IF statements.
another way is to consider a command button click on a form which will fire a code for all the calculation.
A function which will iterate through all the 18 Loss Ratio fields and will evaluate accordingly.
 
More commonly you simply store your data raw, and do the math at retrieval time...
On a form, use a calculated field, so a textbox called LossRatioA with a ControlSource of something like...
Code:
=[LossA]/[PremiumA]
In a query...
Code:
SELECT Loss, Premium, Loss / Premium As LossRatio 
FROM Table
...but to store both the data AND the results of calculations on that data is a recipe for internal errors where your data is in conflict with itself, which is a sort of worst-case scenario. Avoid.
 

Users who are viewing this thread

Back
Top Bottom