DAO.Recordset AddNew

Question

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

Please add more details to your question, the goal and process not clear. — Sergey S., Feb 02 '17 at 04:49

score 1 · Accepted Answer · edited Feb 08 '17 at 13:20

1

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

edited Feb 08 '17 at 13:20

SoggyCashew

69
1
4
15

answered Feb 02 '17 at 08:34

Gustav

53,498
7
29
55

Sorry for the late response... I get a error '3061' To few parameters. Expected 1. and debug takes me to... Set rs = CurrentDb.OpenRecordset(Sql) – SoggyCashew Feb 07 '17 at 12:12
That typically indicates a missing or misspelled field name (or table name). – Gustav Feb 07 '17 at 12:41
One last question before I do. I had a field EmployeeID that needed updated in the tbluBoughtVacation as well. But im running this from a button and I want to pull the data from a text box for the EmployeeID field. Something like... Set db = CurrentDb Set rs = db.OpenRecordset("tbluBoughtVacation", dbOpenDynaset) rs.AddNew rs!EmployeeID = Me.txtEmployeeID – SoggyCashew Feb 08 '17 at 11:25

DAO.Recordset AddNew

1 Answers1

Linked