Add multiple records on one form with auto-populate

[lydonw]

I have a form with StartDate, 3 Comboboxes (Employee, Consumer, Service), and a button. What I'm struggling with is code to do the following:
User enters a start date (mandatory), and values in all of the comboboxes (mandatory), then hits the button. The subform then creates 7 records with those default values, with each record dated according to StartDate, where each record is a day of the week (sun-sat).

I've attached what I have currently, which is hardly anything - I can't figure out how to create the records on the click event. Also attached is the desired result after clicking Go given the values on the main form.

I also need to add a button to submit these records, which on a click will add the records to the table while deleting (or undoing) any records that do not have all field complete.

*note - A consumer can only have a particular service once per day, so there should never be duplicates of Consumer+Service+Date. I figured I could pretty easily add a field to the table that uses these values to create a uniqueID, then prevent duplicates, but I also need the form to display these values if within the range and values (excluding Employee) in the main form.

I have some code (from Stackoverflow) but I'm not sure of how to use the counter/calendar table it suggests, or what field "AddDate" is referencing.

Private Sub Go_Click()
Dim rs As DAO.Recordset
Dim sSQL As String
Dim sSDate As String
Dim sEDate As String

    sSDate = "#" & Format(Me.StartDate, "yyyy/mm/dd") & "#"
    sEDate = "#" & Format(Me.StartDate + 7, "yyyy/mm/dd") & "#"

    sSQL = "SELECT * FROM tblTimeCardHours WHERE DateWorked Between " & sSDate _
        & " AND " & sEDate

    Set rs = CurrentDb.OpenRecordset(sSQL)

    If rs.RecordCount < 7 Then
        AddRecords sSDate, sEDate
    End If

    Me.frmTimesheetEntrySubform.Form.RecordSource = sSQL
End Sub



Sub AddRecords(sSDate, sEDate)
''Uses counter table with integers from 0 to
''highest required number
''Another useful table is a calendat table, which would
''save some work here.

    sSQL = "INSERT INTO tblTimeCardHours (DateWorked) " _
        & "SELECT AddDate FROM " _
        & "(SELECT " & sSDate _
        & " + [tblTimeCardHours.TimeCardDetailID] AS AddDate " _
        & "FROM [tblTimeCardHours] " _
        & "WHERE " & sSDate _
        & "+ [tblTimeCardHours.TimeCardDetailID] Between " & sSDate _
        & " And " & sEDate & ") a " _
        & "WHERE AddDate NOT In (SELECT DateWorked FROM tblTimeCardHours)"

    CurrentDb.Execute sSQL, dbFailOnError
End Sub

 

[lydonw]

Ah, no hits. Understandable. Even if someone can't actually help me (or doesn't want to), any direction with which to help me focus my search? I've looked around for things about adding multiple records from one form and things, but so far I haven't found anything that I can use - or at least I haven't recognized it as such.

 

[Pat Hartman]

You would need to run 7 insert queries or use DAO/ADD with a code loop (preferable). Look for the AddNew method. I have posted examples of the code you need. The reason that the AddNew method is prefered in this case is because it only requires you to run a single query rather than 7 and there is a fair amount of overhead involved in opening a query.

 

[lydonw]

Excellent! Direction! Ok, I'll take a look at what you've posted.

 

[lydonw]

Ok, these posts have code with AddNew being used, any particular one I should focus on? I'm absolutely new at code, so I'm basically learning a new language (am literally? - whatever).

Adding Multiple Records
Populate Subform Record
Insert New Records with Range of Values
Multiselect Listbox Add Records

I've been looking at them all a bit already, but between figuring out what each bit of text means/does, it's a challenge. I feel like I've been pushed into the deep end and am drowning.