create new record in subform while still displaying employee information (trigger)

If  i got this right you want for your employess to pick a day and create records for each of them...
If this is the case all you need is an append query that will use the date criteria to append for every day you select the matching records...something like
WorkingDate         EmployeeID        Rest of your Data
06/25/2018                    3                             .........
06/25/2018                    7                             .........
06/25/2018                   18                            .........

You can use the RecordsetClone of the subform in the AfterUpdate event of the date picker:

  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field
  Dim lngLoop     As Long
  Dim lngCount    As Long

  Set rstInsert = Me!NameOfSubformControl.Form.RecordsetClone
  Set rstSource = rstInsert.Clone
  With rstSource
    lngCount = .RecordCount
    For lngLoop = 1 To lngCount
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              ElseIf .Name = "PayDate" Then
                ' Insert new pay date.
                rstInsert.Fields(.Name).Value = Me!PayDatePicker.Value
              Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With
          Next
        .Update
      End With
      .MoveNext
    Next
    rstInsert.Close
    .Close
  End With
  
  Set rstInsert = Nothing
  Set rstSource = Nothing

Select allOpen in new window

John,

That's what I have.  I have a simple query based off the Employee table that displays the records as:

EmpID     LastName     FirstName     HrsWrkd
140132   Alascio           Jacquie          35.57
140204    Averiod         Donna            16.34

On an unbound form I have a text field txtPayDate with a date picker
and 2 command buttons

One to clear the subform and add the current pay period from the date picker
and the second command button runs an append query to append the information into the LEAVE table.

The code is as follows:

Private Sub CmdClearSub_Click()
Me!subformPTees.Form.Requery
DoCmd.SetWarnings (warningsoff)
DoCmd.OpenQuery ("qryclearPTLeave")
DoCmd.SetWarnings (warningson)
Me!subformPTees.Form.Requery
end sub


Private Sub UpdateLeave_Click()
If IsNull(Me!subformPTees.Form!HrsWrkd) Then
DoCmd.Restore
Else
If Not IsNull(DLookup("[paydate]", "[Leave]", "[paydate]=#" & Me!subformPTees.Form![paydate] & "#")) Then
MsgBox ("You've already Entered S&S Leave for this pay period")
Me.CmdClearSub.SetFocus
Me.UpdateLeave.Enabled = False
'Else
End If
End If
Me!subformPTees.Form.Requery
DoCmd.SetWarnings (warningsoff)
DoCmd.OpenQuery ("QryAppendLeave")
DoCmd.SetWarnings (warningson)
End Sub

The update leave command button should find duplicates and prevent another append.  It's not working.

Gustav,

I've tried your code and I get a run time 3022 error saying that the changes I want to make would create duplicate values in the index, primary key or relationship.  It hangs up on the .update

Pat,

I had looked at ADP to see if there was a report I could use for this purpose but they don't come down as a list that I can easily import into access.  ADP does not discern whether an employee is part time or full time, as far as they're concerned they are employees.

I have two buttons on the form because when the form first opens it contains the information from the last data entry.  So, my thought was to clear the form and add the new date and handle the null problem in hrswrkd, then before the information is submitted it would check for duplicates before running the append query.

If you keep your own table of who is full or part time, can you then use the ADP data?  Retyping should only be a last resort.

I've tried your code and I get a run time 3022 error saying that the changes I want to make would create duplicate values in the index, primary key or relationship.

Well, then correct that - that's what the middle section is doing.
So:

• insert a line with Debug.Print to study the source field names and values

• insert yet a filter to catch the offending field and insert a proper value

It could go like this (of course, your field is not named SomeUniqueField):

    For Each fld In rstSource.Fields
            With fld
              ' Watch field names and values.
              Debug.Print .Name, .Value

              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.

              ElseIf .Name = "SomeUniqueField" Then
                ' Provide some other unique value …
                rstInsert.Fields(.Name).Value = NewUniqueValue

              ElseIf .Name = "PayDate" Then
                ' Insert new pay date.
                rstInsert.Fields(.Name).Value = Me!PayDatePicker.Value
              Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With

Select allOpen in new window

Have in mind, that operating on the RecordsetClone is way faster than calling queries.

I contacted ADP and they wrote a custom report that comes down as an excel spreadsheet.  I can import it into access that way.  I will be posting more questions on this topic as I proceed forward.  I apologize in getting back to all with regards to this open question.  Thank you all for your help.

You're welcome.