Using Class Module in VBA give error.

Question

Hi,

I get an "object variable or With block variable not set " error in the following code. Th line that gets the error is all = GetPayAllocation(rsPrj, 10, 1)

If I check the properties of the all variable they have values.

Any ideas?

Public Function tmptest1()

Dim rsPrj As Recordset
If Not Connection Then Exit Function
gSQL = "SELECT * FROM Projects WHERE ProjectID=7893"
If Not GetODBCRecordset(gSQL, rsPrj) Then Exit Function
Dim all As PayAllocation
all = GetPayAllocation(rsPrj, 10, 1)

Debug.Print all.ManagementFee
CloseALL

End Function

Public Function GetPayAllocation(rsPrj As Recordset, invHours As Double, invweeksofpay As Integer) As PayAllocation
On Error GoTo ErrHandler

    Dim all As PayAllocation
    Set all = New PayAllocation
    If Not all.Calculate(rsPrj, invHours, invweeksofpay) Then GoTo ErrExit

    Set GetPayAllocation = all

ErrExit:
    Exit Function
ErrHandler:
    GeneralErrorHandler ("GetPayAllocation")
    Resume ErrExit
End Function

This the PayAllocation class module

Public PayRate As Double Public Margin As Double Public ManagementFee As Double Public PayrollTax As Double Public AgencyCommission As Double Public Total As Double

Public Function Calculate(rsPrj As Recordset, invHours As Double, invweeksofpay As Integer) As Boolean

On Error GoTo ErrHandler

Dim multiplier As Double
multiplier = GetMultiplierValue(rsPrj, invHours, invweeksofpay)

PayRate = GetValue(multiplier, rsPrj!PayRateInclSuper)
Total = PayRate

If rsPrj!MarginRateInclInPayRate = False Then
    If rsPrj!MarginRatePercent Then
        Margin = GetValue(rsPrj!MarginRate, PayRate)
    Else
        Margin = GetValue(multiplier, rsPrj!MarginRate)
    End If
    Total = Total + Margin
End If

If rsPrj!LMFInclInPayRate = False Then
    If rsPrj!LMFPercent Then
        ManagementFee = GetValue(rsPrj!LMF, PayRate)
    Else
        ManagementFee = GetValue(multiplier, rsPrj!LMF)
    End If
    Total = Total + ManagementFee
End If

If rsPrj!PayrollTaxInclInPayRate = False Then
    If rsPrj!PayrollTaxPercent Then
        PayrollTax = GetValue(rsPrj!PayrolltaxAmount, PayRate)
    Else
        PayrollTax = GetValue(multiplier, rsPrj!PayrolltaxAmount)
    End If
    Total = Total + PayrollTax
End If

If rsPrj!AgencyCommInclInPayRate = False Then
    If rsPrj!AgencyCommPercent Then
        AgencyCommission = GetValue(rsPrj!AgencyComm, PayRate)
    Else
        AgencyCommission = GetValue(multiplier, rsPrj!AgencyComm)
    End If
    Total = Total + AgencyCommission
End If


If rsPrj!MarginRateOnTop Then
    If rsPrj!MarginRatePercent Then
        Margin = GetValue(rsPrj!MarginRate, Total)
    Else
        Margin = GetValue(multiplier, rsPrj!MarginRate)
    End If
    Total = Total + Margin
End If

If rsPrj!LMFOnTop Then
    If rsPrj!LMFPercent Then
        ManagementFee = GetValue(rsPrj!LMF, Total)
    Else
        ManagementFee = GetValue(multiplier, rsPrj!LMF)
    End If
    Total = Total + ManagementFee
End If

If rsPrj!PayrollTaxOnTop Then
    If rsPrj!PayrollTaxPercent Then
        PayrollTax = GetValue(rsPrj!PayrolltaxAmount, Total)
    Else
        PayrollTax = GetValue(multiplier, rsPrj!PayrolltaxAmount)
    End If
    Total = Total + PayrollTax
End If

If rsPrj!AgencyCommOnTop Then
    If rsPrj!AgencyCommPercent Then
        AgencyCommission = GetValue(rsPrj!AgencyComm, Total)
    Else
        AgencyCommission = GetValue(multiplier, rsPrj!AgencyComm)
    End If
    Total = Total + AgencyCommission
End If

Calculate = True

ErrExit: Exit Function ErrHandler: Calculate = False Resume ErrExit End Function

Private Function GetMultiplierValue(rsPrj As Recordset, invHours As Double, invweeksofpay As Integer) As Double

Dim value As Double

Select Case rsPrj!HourlyDailyMthly
    Case "Hourly"
        value = invHours
    Case "Daily"
        value = invHours
    Case "Weekly"
        value = CDbl(invweeksofpay)
    Case "Monthly"
End Select

GetMultiplierValue = value

End Function

Private Function GetValue(multiplier As Double, amount As Double)

GetValue = format(multiplier * amount, "0.00")

End Function

Answer 1

It should have been.

Set all = GetPayAllocation(rsPrj, 10, 1)