How can I create a new (mostly duplicate) record based on a value in 1 field of the current record?

[BFauska]

I'm working on a payroll form that works great currently but I'd like to automate the task of hours over 8 being overtime. I set the beforeupdate event of the form to check the value in the HourQuantity field and I've managed to make that work to put up a message box and offer some options but after the message box I'm successfully changing the hour quantity that the user entered to 8 (if it was more) and when I try to create a new record to put the remainder of hours into it says it can't go to that record. My form is set to allow new entries and all that stuff (as I said it works if I just keep entering new record after new record, I just can't get it to happen in the BeforeUpdate event).



I'd like to do the tests for the HourQuantity value, set some variables based on the other values of the record then create a new record and apply those variables to the values in the new record.

I'm nearly positive the problem is that I'm not closing the current record or something before
"DoCmd.GoToRecord , , acNewRec"

Thoughts?
Need more info? (probably but the other stuff all seems to be working and could be rabbit hole of clarifying so I'm hoping there is an obvious but under-publicized problem).

[June7]

What event are you using to save the record and trigger the BeforeUpdate? A button Click? Post that code. Instead of form BeforeUpdate, I would probably put all the code in Click event. I've never used form BeforeUpdate event.

[BFauska]

I'm trying to make it automatic as the user "tabs" or "Enters" to their next entry. I could move it to trigger off a different event but if I make it a reaction to a mouse click it meddles with the natural flow of data input on this form. Since I basically learn as I go I've been adding to the event step by step and had the message box working fine in this event but when I added the part to create a new record it stopped working.

Here's the code (I make no claims of knowing or using best practices...anywhere):

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
    
    
    If Me.HourQuantity.Value > 8 And Me.Straight_Overtime.Value = 1 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
    
    '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")
            Me.HourQuantity.Value = 8
            
            
            'Create new record for the Double Time
                    
            DoCmd.GoToRecord , , acNewRec
           
            Me.WorkDay.Value = VarHqDate
            Me.HourQuantity.Value = VarHqDt
            Me.Show.Value = VarHqShow
            Me.Straight_Overtime.Value = 2
            Me.PayRate.Value = VarHqRate
            Me.FETR.Value = VarHqFETR
            Me.LeoKTrainee.Value = VarHqLeo
            Me.AccountCode.Value = VarHqAcct
            Me.Department.Value = VarHqDpt
            Me.CmbPayType.Value = VarHqType
                        
            Else
            MsgBox ("You're doing something odd")
            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 ")
            Me.HourQuantity.Value = 8
            Else
            MsgBox ("You're doing something slightyly less odd")
            End If
        End If
    End If
    
End Sub

While looking around the interwebs for other options and solutions it dawned on me that I should mention this is for data entered in a continuous form.

[Bulzie]

You could change the hours to 8, save it then insert the remainder into a record directly into the table using Insert command.

DoCmd.RunSQL "INSERT INTO YourTable (Straight_Overtime, AccountCode, CmbPayType)" & "Values (2, VarHqAcct, VarHqType)"

Do you have to create a new record for the overtime or do you record it on the same record they enter?

[BFauska]

It ends up as another record with a specific pay rate and account code and other details that pertain to overtime. The current system is that they would enter 8 and all the details and then enter a new record with 4 (or however many) and all the details. They frequently work 10 hour days with all the other details being the same (except the account code used for OT) so having them just input the total hours would be great. They also typically can see all their entries on the form as they do them (if they do a couple days at once for example) which is the reason I didn't want to input them directly to the table, though I guess I could do that then do an update on the form to show all the new records.

I'll give that a try and see how it works.

[June7]

In our T&A system, users just input start and end times for the day and lunch and the system calculates the hours.

But that's not really the procedure I was asking about. Do you have a button to 'Save' record? As I said, I've never used form BeforeUpdate. I use button Click event.

[Bulzie]

Can you just limit the hours field to 8 if it is not an overtime type of record? In the AfterUpdate event of the hours field, if > 8 and Type = "Overtime", give them msgbox

Hours field AfterUpdate Event:

If me.Hours > 8 and Me.PayType <> "Overtime" then
Msgbox "Regular hours cannot exceed 8. Please add a separate entry for " & Me.Hours - 8 & " overtime hours."
Me.Hours = 8
End If

Of course if you want to add the overtime record automatically, then can do that again in code.

[BFauska]

Since I posted the question and someone like me in some parallel universe may be looking for an answer, here's what I ended up doing.
The gist is that I inserted new records into the table in the background using SQL then refresh the form and leave the cursor in a new record ready to go. The downside we've seen in practice is that, the way I've created it (and it may be a thing that could be hunted down and fixed but it hasn't driven my users crazy yet) if you change an existing record to one that this process then runs on you end up with a duplicate in the mix and extra hours on the timesheet (which we catch because we review and it rarely happens because we don't often change records we mostly just add new ones). I think this is because the process is happening before the record is saved typically and therefore the only ones that end up existing are the ones the code inserts and the one the user had typed disappears (which is good) if you modify an existing record then it just stays put and new records are created in addition to it. I think eventually I'll go back and adjust the process to check if the user is modifying or creating a record and if they are modifying it will delete the old when it creates the new, but for now it's so rare and easy to live with/catch I've got higher priorities.
Anyway, here's the probably grotesque 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