CODEWITHSUNDEEP
formsms-accessvba
SoggyCashew
SoggyCashew

Reputation: 69

DAO.Recordset AddNew

I wanted to know if you can and how do you keep adding records or loop records to a table until I reach a certain date 1/1/2121 for a field? I changed my code to a sub and I could call it like FilltbluBoughtVacation 2013, 2021

BUT I thought that would work and im getting an error '3134' Syntax error in insert into statement and debug takes me to 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

Upvotes: 0

Views: 8317

Answers (1)

Gustav
Gustav

Reputation: 55831

It is too little for two functions, and calling SQL in a loop is way too slow. Thus:

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

Upvotes: 1

Related Questions