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