DAO.Recordset AddNew

Question

我想知道您是否可以，以及如何不断向表中添加记录或循环记录，直到字段达到某个日期1/1/2121？我将代码更改为sub，我可以将其命名为FilltbluBoughtVacation 2013,2021

但是我认为这是可行的，我得到了一个错误'3134‘语法错误在insert into语句和debug把我带到CurrentDb.Execute strSql。

Public Sub FilltbluBoughtVacation(StartYear As Integer, EndYear As Integer)

Dim BoughtYear As Date
Dim CurrentYear As Integer

For CurrentYear = StartYear To EndYear
    BoughtYear = CDate("01/01/" & CurrentYear)
    InsertBoughtVacation BoughtYear

Next CurrentYear
End Sub

Public Sub InsertBoughtVacation(BoughtVacDate As Date)
Dim strSql As String
strSql = "Insert into [tbluBoughtVacation] ([BoughtVacDate]) values (#" & Format([BoughtVacDate], "mm/dd/yyyy") & "# ,)"
Debug.Print strSql
CurrentDb.Execute strSql

End Sub

Answer 1

这对于两个函数来说太少了，而且在循环中调用SQL也太慢了。因此：

Public Sub FilltbluBoughtVacation(StartYear As Integer, EndYear As Integer)

    Dim rs AS DAO.Recordset

    Dim Sql As String
    Dim CurrentYear As Integer

    Sql = "Select Top 1 EmployeeID, BoughtYear From tbluBoughtVacation"
    Set rs = CurrentDb.OpenRecordset(Sql)

    For CurrentYear = StartYear To EndYear
        rs.AddNew
            rs("EmployeeID").Value = Me!txtEmployeeID.Value 
            rs("BoughtYear").Value = DateSerial(CurrentYear, 1, 1)
        rs.Update
    Next
    rs.Close

    Set rs = Nothing

End Sub