How to convert and private subroutine into a public one

[blogmad]

Hi,
I guess this is a bit of a newby question. I have 2 rather long but identical subroutines attached to 2 forms. I am going to be creating more forms that will use this subroutine, so I would like to have 1 public routine that I can call from all forms, rather then having to update several private routines, every time I make a change.

I tried just copying over the subroutine, but that didnt work, so I guess I have to recode parts of the subroutine to work in the public routine?
Here is the code as it is in the private routine:
(if anybody feels like providing me with a less convoluted way of writing this routine, I would be most grateful)

Private Sub CalcIncentives()


Dim Totalcost
If IsNull([Cost_to_Company]) Then
[Cost_to_Company] = 0
End If
If IsNull([Cost_in_Euros]) Then
[Cost_in_Euros] = 0
End If
Totalcost = 0
Totalcost = [Cost_to_Company] + [Cost_in_Euros]
If Totalcost < 26 And Totalcost > 0 Then
[Flight_Writeoff] = 1
[car_Writeoff] = 0
[insurance_Writeoff] = 0
ElseIf Totalcost >= 26 And Totalcost < 51 Then
[Flight_Writeoff] = 2
[car_Writeoff] = 0
[insurance_Writeoff] = 2
ElseIf Totalcost >= 51 And Totalcost < 76 Then
[Flight_Writeoff] = 2
[car_Writeoff] = 1
[insurance_Writeoff] = 2
ElseIf Totalcost >= 76 And Totalcost < 101 Then
[Flight_Writeoff] = 2
[car_Writeoff] = 1
[insurance_Writeoff] = 4
ElseIf Totalcost >= 101 And Totalcost < 151 Then
[Flight_Writeoff] = 3
[car_Writeoff] = 1
[insurance_Writeoff] = 0
ElseIf Totalcost >= 151 And Totalcost < 201 Then
[Flight_Writeoff] = 3
[car_Writeoff] = 1
[insurance_Writeoff] = 2
ElseIf Totalcost >= 201 And Totalcost < 251 Then
[Flight_Writeoff] = 4
[car_Writeoff] = 1
[insurance_Writeoff] = 0
ElseIf Totalcost >= 251 And Totalcost < 301 Then
[Flight_Writeoff] = 4
[car_Writeoff] = 1
[insurance_Writeoff] = 2
ElseIf Totalcost >= 301 And Totalcost < 351 Then
[Flight_Writeoff] = 2
[car_Writeoff] = 2
[insurance_Writeoff] = 4
ElseIf Totalcost >= 351 And Totalcost < 401 Then
[Flight_Writeoff] = 3
[car_Writeoff] = 2
[insurance_Writeoff] = 0
ElseIf Totalcost >= 401 And Totalcost < 451 Then
[Flight_Writeoff] = 4
[car_Writeoff] = 2
[insurance_Writeoff] = 0
ElseIf Totalcost >= 451 And Totalcost < 501 Then
[Flight_Writeoff] = 4
[car_Writeoff] = 2
[insurance_Writeoff] = 2
ElseIf Totalcost >= 501 And Totalcost < 551 Then
[Flight_Writeoff] = 4
[car_Writeoff] = 2
[insurance_Writeoff] = 4
ElseIf Totalcost >= 551 And Totalcost < 601 Then
[Flight_Writeoff] = 5
[car_Writeoff] = 2
[insurance_Writeoff] = 0
ElseIf Totalcost >= 601 And Totalcost < 651 Then
[Flight_Writeoff] = 5
[car_Writeoff] = 2
[insurance_Writeoff] = 2
ElseIf Totalcost >= 651 And Totalcost < 701 Then
[Flight_Writeoff] = 5
[car_Writeoff] = 2
[insurance_Writeoff] = 4
ElseIf Totalcost >= 701 And Totalcost < 751 Then
[Flight_Writeoff] = 5
[car_Writeoff] = 2
[insurance_Writeoff] = 6
ElseIf Totalcost >= 751 And Totalcost < 801 Then
[Flight_Writeoff] = 2
[car_Writeoff] = 3
[insurance_Writeoff] = 4
ElseIf Totalcost >= 801 And Totalcost < 851 Then
[Flight_Writeoff] = 3
[car_Writeoff] = 3
[insurance_Writeoff] = 0
ElseIf Totalcost >= 851 And Totalcost < 901 Then
[Flight_Writeoff] = 4
[car_Writeoff] = 3
[insurance_Writeoff] = 0
ElseIf Totalcost >= 901 And Totalcost < 951 Then
[Flight_Writeoff] = 4
[car_Writeoff] = 3
[insurance_Writeoff] = 2
ElseIf Totalcost >= 951 And Totalcost < 1001 Then
[Flight_Writeoff] = 4
[car_Writeoff] = 3
[insurance_Writeoff] = 4
ElseIf Totalcost >= 1001 And Totalcost < 1051 Then
[Flight_Writeoff] = 5
[car_Writeoff] = 3
[insurance_Writeoff] = 0
ElseIf Totalcost >= 1051 And Totalcost < 1101 Then
[Flight_Writeoff] = 5
[car_Writeoff] = 3
[insurance_Writeoff] = 2
ElseIf Totalcost >= 1101 And Totalcost < 1151 Then
[Flight_Writeoff] = 5
[car_Writeoff] = 3
[insurance_Writeoff] = 4
ElseIf Totalcost >= 1151 And Totalcost < 1201 Then
[Flight_Writeoff] = 6
[car_Writeoff] = 3
[insurance_Writeoff] = 0
ElseIf Totalcost >= 1201 And Totalcost < 1251 Then
[Flight_Writeoff] = 6
[car_Writeoff] = 3
[insurance_Writeoff] = 2
ElseIf Totalcost >= 1251 And Totalcost < 1301 Then
[Flight_Writeoff] = 6
[car_Writeoff] = 3
[insurance_Writeoff] = 4
ElseIf Totalcost >= 1301 And Totalcost < 1351 Then
[Flight_Writeoff] = 6
[car_Writeoff] = 4
[insurance_Writeoff] = 2
ElseIf Totalcost >= 1351 And Totalcost < 1401 Then
[Flight_Writeoff] = 6
[car_Writeoff] = 4
[insurance_Writeoff] = 6
ElseIf Totalcost >= 1401 And Totalcost < 1451 Then
[Flight_Writeoff] = 7
[car_Writeoff] = 4
[insurance_Writeoff] = 2
ElseIf Totalcost >= 1451 And Totalcost < 150001 Then
[Flight_Writeoff] = 7
[car_Writeoff] = 4
[insurance_Writeoff] = 4

End If


End Sub

 

[Bat17]

I would dump the figures in a table, you only need the lower bounds, 0, 26, 51, 76, 101, 151 ........
Add fields for the writeoffs and then you can use domain functions to dig out the values you need.

Assuming the Fields are TotCost, Car, flight, Insurance and that the table is called tblWriteOff then you could use something like:-

Dim intRiskBand As Integer
Dim intRisk As Integer

intRisk = Nz([Cost_to_Company], 0) + Nz([Cost_in_Euros], 0)

intRiskBand = DMax("TotCost", "tblWriteOff", "TotCost < " & intRisk)
[Flight_Writeoff] = DLookup("Flight", "tblWriteOff", "TotCost = " & intRiskBand)
[car_Writeoff] = DLookup("car", "tblWriteOff", "TotCost = " & intRiskBand)
[insurance_Writeoff] = DLookup("Insurance", "tblWriteOff", "TotCost = " & intRiskBand)

HTH

Peter

 

[blogmad]

thanks very much for this. It works a treat.
I am getting an error though when the form opens (invalid use of null) which is caused by the call to this routine. not sure why this is happening, but the routine works in any event it appears.

 

[Bat17]

I think the first boundry may need to be -1 rather than 0 to stop a problem when total cost is 0 as the code will be looking for the max boundry that is less than 0!

HTH

Peter

 

[blogmad]

thanks for that Peter,
I'm sorted now