Insert multiple records with a date range in MS Access

Question

hoping someone can help? I'm fairly new to Access 2016 and have been tasked with building a very simple booking system for our school's breakfast and after school clubs. I have a table with a list of Children (primary key is ChildID), another table (CLUBS) lists the 5 clubs available, and a third table (BOOKINGS) connects the children to the clubs (ChildID, ClubID, DateRequested)

I have a simple form that enables me to select a child's name from a drop down box, then from a list choose a club, then enter the date required. This saves the record to the Bookings table.

This works fine, however to make it easier to use...I've added unbound Start and End Date fields in the form with a view to being able to quickly book a child in over a term..i.e. rather than having to add each day individually, I enter the child's name, choose a club and then enter start and end dates. Multiple records are created in the booking table with the Child ID, Club ID identical, but the DateRequested field varies.

We do need to store a record in the Bookings table for the child on each date so we can print a register for each day..as well as for invoicing/reporting.

From looking at VBA...I think I need to use the INSERT INTO command? Is the best way to do it? Also I need to make sure that dates within the range that are Sat/Sunday are ignored.

I'd really appreciate any guidance on this and pointers to which commands would work best...

Answer 1

This is where DAO shines. It is so much faster to run a loop adding records than calling a Insert Into multiple times.

Here is how:

Public Function PopulateBokings()

    Dim rsBookings  As DAO.Recordset
    Dim NextDate    As Date

    Set rsBookings = CurrentDb.OpenRecordset("Select Top 1 * From Bookings")

    NextDate = Me!StartDate.Value
    While DateDiff("d", NextDate, Me!EndDate.Value) >= 0
        If Weekday(NextDate, vbMonday) > 5 Then
            ' Skip Weekend.
        Else
            rsBookings.AddNew
                rsBookings!ChildrenId.Value = Me!ChildrenId.Value
                rsBookings!ClubsId.Value = Me!ClubId.Value
                rsBookings!DateRequested.Value = NextDate
            rsBookings.Update
        End If
        NextDate = DateAdd("d", 1, NextDate)
    Wend
    rsBookings.Close

    Set rsBookings = Nothing

End Function

Paste the code into the code module of the form, adjust the field and control names to those of yours, and call the function from the Click event of a button.