Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'Check for hours over 8 and prompt for change to OT
Dim VarHqOT As Long
Dim VarHqDt As Long
Dim VarHqDate As Date
Dim VarHqShow As Long
Dim VarHqRate As Long
Dim VarHqFETR As Variant
Dim VarHqLeo As Variant
Dim VarHqDpt As Long
Dim VarHqType As Variant
Dim VarHqTS As Long
Dim DtLine As String
Dim OtLine As String
Dim StLine As String
Dim VarHqMpl As Single
If Me.HourQuantity.Value > 8 And Me.StraightOrOvertime.Value = 1 And Me.Department.Value <> 44100 Then
VarHqOT = Me.HourQuantity.Value - 8
VarHqDate = Me.WorkDay.Value
VarHqShow = Me.Show.Value
VarHqRate = Me.PayRate.Value
VarHqFETR = Me.FETR.Value
VarHqLeo = Me.LeoKTrainee.Value
VarHqDpt = Me.Department.Value
VarHqType = Me.CmbPayType.Value
VarHqTS = Me.Parent!TxtTimeSheetNumber
VarHqStAcct = Me.AccountCode.Value
'Set overtime version of account type into variable
Select Case Me.AccountCode.Value
Case "2033"
VarHqAcct = 2033
Case "6104"
VarHqAcct = 6123
Case "6108"
VarHqAcct = 6128
Case "6203"
VarHqAcct = 6223
Case "6207"
VarHqAcct = 6227
End Select
If VarHqOT > 4 Then
VarHqOT = 4
VarHqDt = Me.HourQuantity.Value - 12
DtOtStResponse = MsgBox("Should that be 8 hours of Straight Time, " & VarHqOT & " as Overtime, and " & VarHqDt & " as Double Time?", vbYesNo)
If DtOtStResponse = vbYes Then
'MsgBox ("Ok, I'll do some math that includes Double Time")
DoCmd.SetWarnings False
'Create new record for the Double Time
VarHqMpl = 2
DtLine = "INSERT INTO HourDetails (WorkDay, HourQuantity, PayRate, AccountCode, Department, Show, FETR, LeoKTrainee, TimeSheetNumber, PayType, StraightOrOvertime)" & _
" VALUES ('" & VarHqDate & "','" & VarHqDt & "','" & VarHqRate & "','" & VarHqAcct & "','" & VarHqDpt & "','" & VarHqShow & "','" & VarHqFETR & "','" & VarHqLeo & "','" & VarHqTS & "','" & VarHqType & "','" & VarHqMpl & "');"
DoCmd.RunSQL DtLine
'Create new record for the Overtime
VarHqMpl = 1.5
OtLine = "INSERT INTO HourDetails (WorkDay, HourQuantity, PayRate, AccountCode, Department, Show, FETR, LeoKTrainee, TimeSheetNumber, PayType, StraightOrOvertime)" & _
" VALUES ('" & VarHqDate & "','" & VarHqOT & "','" & VarHqRate & "','" & VarHqAcct & "','" & VarHqDpt & "','" & VarHqShow & "','" & VarHqFETR & "','" & VarHqLeo & "','" & VarHqTS & "','" & VarHqType & "','" & VarHqMpl & "');"
DoCmd.RunSQL OtLine
'Create new record for the Straight Time
VarHqMpl = 1
StLine = "INSERT INTO HourDetails (WorkDay, HourQuantity, PayRate, AccountCode, Department, Show, FETR, LeoKTrainee, TimeSheetNumber, PayType, StraightOrOvertime)" & _
" VALUES ('" & VarHqDate & "', 8,'" & VarHqRate & "','" & VarHqStAcct & "','" & VarHqDpt & "','" & VarHqShow & "','" & VarHqFETR & "','" & VarHqLeo & "','" & VarHqTS & "','" & VarHqType & "','" & VarHqMpl & "');"
DoCmd.RunSQL StLine
'turn woarning back on
DoCmd.SetWarnings True
'refresh the form by browseing to it again
DoCmd.BrowseTo acBrowseToForm, "TimeSheetEdit", "HourEntryMenu.NavigationSubform", "[TimeSheetNumber]=" & VarHqTS, , acFormEdit
'start new record
DoCmd.RunCommand (acCmdRecordsGoToNew)
Else
MsgBox ("If you say so.")
End If
Else
OtStResponse = MsgBox("Should that be 8 hours of Straight Time and " & VarHqOT & " of Overtime?", vbYesNo)
If OtStResponse = vbYes Then
'MsgBox ("Ok, I'll do some math ")
DoCmd.SetWarnings False
'Create new record for the Overtime
VarHqMpl = 1.5
OtLine = "INSERT INTO HourDetails (WorkDay, HourQuantity, PayRate, AccountCode, Department, Show, FETR, LeoKTrainee, TimeSheetNumber, PayType, StraightOrOvertime)" & _
" VALUES ('" & VarHqDate & "','" & VarHqOT & "','" & VarHqRate & "','" & VarHqAcct & "','" & VarHqDpt & "','" & VarHqShow & "','" & VarHqFETR & "','" & VarHqLeo & "','" & VarHqTS & "','" & VarHqType & "','" & VarHqMpl & "');"
DoCmd.RunSQL OtLine
'Create new record for the Straight Time
VarHqMpl = 1
StLine = "INSERT INTO HourDetails (WorkDay, HourQuantity, PayRate, AccountCode, Department, Show, FETR, LeoKTrainee, TimeSheetNumber, PayType, StraightOrOvertime)" & _
" VALUES ('" & VarHqDate & "', 8,'" & VarHqRate & "','" & VarHqStAcct & "','" & VarHqDpt & "','" & VarHqShow & "','" & VarHqFETR & "','" & VarHqLeo & "','" & VarHqTS & "','" & VarHqType & "','" & VarHqMpl & "');"
DoCmd.RunSQL StLine
DoCmd.SetWarnings True
DoCmd.BrowseTo acBrowseToForm, "TimeSheetEdit", "HourEntryMenu.NavigationSubform", "[TimeSheetNumber]=" & VarHqTS, , acFormEdit
DoCmd.RunCommand (acCmdRecordsGoToNew)
Else
MsgBox ("If you say so.")
End If
End If
End If
End Sub