在 MS Access 中插入具有日期范围的多条记录 [英] Insert multiple records with a date range in MS Access

问题描述

希望有人能帮忙?我是 Access 2016 的新手，我的任务是为我们学校的早餐和课后俱乐部构建一个非常简单的预订系统.我有一个包含儿童列表的表(主键是 ChildID)，另一个表(CLUBS)列出了 5 个可用的俱乐部，第三个表(BOOKINGS)将孩子们连接到俱乐部(ChildID、ClubID、DateRequested)

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)

我有一个简单的表格，可以让我从下拉框中选择一个孩子的名字，然后从列表中选择一个俱乐部，然后输入所需的日期.这会将记录保存到 Bookings 表中.

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.

这很好用，但是为了更容易使用......我在表单中添加了未绑定的开始和结束日期字段，以便能够在一个学期内快速预订一个孩子......即我不必单独添加每一天，而是输入孩子的名字，选择一个俱乐部，然后输入开始和结束日期.在预订表中创建了多个记录，其中 Child ID、Club ID 相同，但 DateRequested 字段不同.

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.

我们确实需要在每个日期为孩子在 Bookings 表中存储一条记录，以便我们可以打印每天的登记表......以及用于发票/报告.

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.

从 VBA 来看...我想我需要使用 INSERT INTO 命令?是最好的方法吗?此外，我需要确保忽略周六/周日范围内的日期.

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

推荐答案

这就是 DAO 的闪光点.运行循环添加记录比多次调用 Insert Into 要快得多.

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

方法如下:

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

将代码粘贴到表单的代码模块中，将字段和控件名称调整为您的名称，并从按钮的Click事件中调用该函数.

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.

这篇关于在 MS Access 中插入具有日期范围的多条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！