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...

With `Insert` you will need to compose a large SQL string and execute it at once. `Recordset.Addnew` is another option, but the former is preferable.You will need a VBA loop in both cases. Consider creating a *stored procedure* (Query in MS-Access), you'll only need to feed it a `ChildId` and a `TermId` and it will do the job on the server. IMO this would be the best option. — A.S.H, Jan 23 '17 at 23:46

score 1 · Answer 1 · answered Jan 24 '17 at 02:37

Consider populating a separate DateRange table that holds all possible dates, like all of 2017. You can build such a table iteratively with dynamic SQL query calls in VBA. And only run this once.

Then, create a stored query that cross joins Children, Club, and DateRange with filters for all by form parameters. This returns all possible date ranges repeating same Child and Club for table append.

VBA

Public Sub PopulateTime()
    Dim i As Integer, StartDate As Date

    CurrentDb.Execute "CREATE TABLE DateRange (" _
                             & " [ID] AUTOINCREMENT PRIMARY KEY," _
                             & " [BookDate] DATETIME)", dbFailOnError

    StartDate = DateSerial(2017, 1, 1)
    For i = 0 To 364
          CurrentDb.Execute "INSERT INTO DateRange ([BookDate])" _
                 & " VALUES (#" & DateAdd("d", i, StartDate) & "#);", dbFailOnError
    Next i

End Sub

SQL

INSERT INTO Bookings (ChildID, ClubID, DateRequested)
SELECT c.ChildID, b.ClubID, d.BookDate
FROM Children c, Clubs b, DateRange d
WHERE c.ChildID = Forms!myformname!ChildID
  AND b.ClubID = Forms!myformname!ClubID
  AND d.BookDate BETWEEN Forms!myformname!StartDate 
                     AND Forms!myformname!EndDate

Gustav · Accepted Answer · 2017-01-24T15:24:03.650

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.

Many thanks Gustav....it worked first time! The only one remaining issue is how to stop non workdays coming through (Saturday/Sunday)...so if the date range is 14 days - only 10 records should be added.... — Mark Tennant, Jan 24 '17 at 15:01

Erik A · Answer 3 · 2018-03-15T09:14:43.363

You can use a sequence generator query to repeatedly insert rows into a table between two parameters.

For this example, the maximum number of days inserted is 999, but this can easily be increased to 9999 or even more.

Inspired by this answer by Gustav:

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
INSERT INTO MyTable(MyDateField)
SELECT DISTINCT [StartDate] - 1+ 100*Abs([Hundreds].[id] Mod 10) + 10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1
FROM MSysObjects As Ones, MSysObjects As Tens, MSysObjects As Hundreds
WHERE [StartDate] - 1+ 100*Abs([Hundreds].[id] Mod 10) + 10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1 Between [StartDate]-1 And [EndDate]

Performance won't be good, but there are multiple advantages using a non-VBA solution.

Insert multiple records with a date range in MS Access

3 Answers3

Linked

Related