Insert multiple records with a date range in MS Access

by

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.

More Related Contents:

  1. ms access vba code for update records through form
  2. How to add default signature in Outlook
  3. Combine rows / concatenate rows
  4. Is it possible to pass parameters programmatically in a Microsoft Access update query?
  5. Getting Error 3340 Query ‘ ‘ is corrupt while executing queries DoCmd.RunSQL
  6. Too few parameters Expected 1, recordset issue
  7. Show “Open File” Dialog
  8. How do I display a messagebox with unicode characters in VBA?
  9. Determining whether an object is a member of a collection in VBA
  10. Distinction between using .text and .value in VBA Access
  11. MS Access VBA Error: Run time error ’70’ Permission Denied
  12. Automatically generating handling of issues
  13. Is it better to use ADO or DAO in Access 2007?
  14. VBA – Folder Picker – set where to start [duplicate]
  15. MS Access: how to compact current database in VBA
  16. How to loop through all controls in a form, including controls in a subform – Access 2007
  17. FileDialog doesn’t work
  18. Working with Unicode file names in VBA (using Dir, FileSystemObject, etc.)
  19. Determine real cause of ODBC failure (error 3146) with ms-access?
  20. Is there an equivalent to Thread.Sleep() in VBA
  21. Exporting MS Access Forms and Class / Modules Recursively to text files?
  22. How to get logged-in user’s name in Access vba? [duplicate]
  23. Pass a parameter into an access report programmatically
  24. finding latest file in a folder and opening it (vba access)
  25. MS Access VBA: Sending an email through Outlook [closed]
  26. SendKeys is messing with my NumLock key via VBA code in Access form
  27. Runtime error 287. Sending Emails through Outlook using VBA in Access
  28. Get list of sub-directories in VBA
  29. When is a MailItem not a MailItem? [closed]
  30. Where does Outlook store the VBA code files?

Leave a Comment