Hi
Thank you for your help in this I am nearly there, I have walked it through as Mark suggested. This is my code and it works fine now:
Private Sub cboRoom_Type_ID_AfterUpdate()
BIO_Room_Type = Me.cboRoom_Type_ID.Column(1)
BIO_Room_Cost_Per_Hour = Me.cboRoom_Type_ID.Column(2)
BIO_Vat = Me.cboRoom_Type_ID.Column(3)
BIO_Total_Duration = (DateDiff("n", [BIO_Start_Time], [BIO_End_Time])) * [BIO_Number_Of_Occurences] / 60
'Debug.Print BIO_Occurence_Duration = DateDiff("n", [BIO_Start_Time], [BIO_End_Time]) \ 60 & "." & Format(DateDiff("n", [BIO_Start_Time], [BIO_End_Time]) Mod 60, "00")
Debug.Print "Starting " & BIO_Start_Time & " ending " & BIO_End_Time & " DateDiff returns (Minutes per session) " & (DateDiff("n", [BIO_Start_Time], [BIO_End_Time]))
Debug.Print "Starting " & BIO_Start_Time & " ending " & BIO_End_Time & " DateDiff returns minutes/60 (hr) " & (DateDiff("n", [BIO_Start_Time], [BIO_End_Time])) / 60
Debug.Print "Total Time " & BIO_Start_Time & " to " & BIO_End_Time & " no times " & BIO_Number_Of_Occurences, (DateDiff("n", [BIO_Start_Time], [BIO_End_Time])) / 60 * [BIO_Number_Of_Occurences]
'Vat calculation'
If BIO_Vat = 0 Then
BIO_Vat_Rate = 0
Else
BIO_Vat_Rate = 0.2
End If
'Sub Total is £0 if the booking has not been cancelled
If BIO_Status_Ref <> "C" Then
Else
BIO_Sub_Total = 0
End If
'Discount calculation '
If txtDiscount_ptr = "No" Then
BIO_Discount_Rate = 0
ElseIf txtDiscount_ptr = "Yes" Then
BIO_Discount_Rate = 0.4
Else
BIO_Discount_Rate = 0.5
End If
'Calulations'
BIO_Sub_Total = BIO_Total_Duration * BIO_Room_Cost_Per_Hour
BIO_Discount_Total = [BIO_Sub_Total] * [BIO_Discount_Rate]
BIO_Net_Total_Exc_Vat = [BIO_Sub_Total] - [BIO_Discount_Total]
BIO_Vat_Total = [BIO_Net_Total_Exc_Vat] * [BIO_Vat_Rate]
'Total'
If BIO_Status_Ref = "C" Then
BIO_Total_Amount_Due = 0
Else
BIO_Total_Amount_Due = [BIO_Net_Total_Exc_Vat] + [BIO_Vat_Total]
End If
End Sub
All results are recorded in the underlying table.
At the moment I have the calculations on the After Update event of the room selection combo, but I need to recalculate on any field changes such as if a time is amended or the number of sessions, Where would you recommend putting them?
Thank you.