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

问题描述

希望有人可以提供帮助吗?我对Access 2016并不陌生，受命为我们学校的早餐和课后俱乐部建立一个非常简单的预订系统. 我有一个带有儿童列表的表(主键是ChildID)，另一个表(CLUBS)列出了可用的5个俱乐部，第三个表(BOOKINGS)将孩子连接到俱乐部(ChildID，ClubID，DateRequested)

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

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

我们确实需要在每个日期的孩子的预订"表中存储一个记录，以便我们可以每天打印一个登记表.以及开票/报告.

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

我真的很感谢任何有关此方面的指导以及最适合使用命令的指针...

解决方案

这是DAO大放异彩的地方.运行循环添加记录的速度比多次调用插入到"要快得多.

方法如下:

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事件中调用该函数.

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

解决方案

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.

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