Insert query not executing inside loop

Question

I'm having trouble running multiple insert queries with VBA, in Access 2016.

I load up the relevant records from tbl_Routes into a DAO.Recordset then iterate through them with the outer loop, then use the inner loop to insert five records (one for each weekday) in tbl_Route_Times for each one record from tbl_Routes.

So, what I want to happen when my code runs is this (from left to right):

(1, 1) (1, 2) (1, 3) (1, 4) (1, 5)
(2, 1) (2, 2) (2, 3) (2, 4) (2, 5)
(3, 1) (3, 2) (3, 3) (3, 4) (3, 5)

...and so on for hundreds of records.

Mechanically, the loops work just fine. It steps through each line just as you'd expect, but it only actually executes the query on the first iteration of the inner loop each time, (1, 1) (2, 1) (3, 1) so on and so forth.

It will insert the record correctly on the first pass through the inner loop, but on subsequent passes through the loop it will hit the .Execute line without doing anything - no record is inserted, no error is thrown, no nothing. It just hits the line and keeps going. Once it runs five times and breaks the inner loop, it'll advance to the next record in rs and do the same thing over again, inserting one record on the first run through the inner loop, but no more.

I've tried this with DoCmd.RunSQL and injecting the values into the query each time, but the result is the same regardless of how the query actually runs.

I rebuilt everything with just a meaningless test integer that increments and that actually works fine, running the query and inserting a record on each iteration of the inner loop. It seems I've isolated the problem is related to loading the records into rs first, then running the insert query, but I don't really know what else to try at this point.

In the snippet below I show how I set up my use of the different objects - if there's something I'm doing wrong that's blocking four out of five of the insert queries from actually running, I'd love to know what it is.

What am I missing here?

Here's the loops themselves:

' For reference...
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSqlSelect)

... 

Do While Not rs.EOF
     Do While dayCount < 6
          With db.CreateQueryDef("", strSqlInsert)
               .Parameters!pRid = rs!routeID
               .Parameters!pDay = dayCount     
               .Parameters!pIn = rs!checkIn  
               .Parameters!pOut = rs!checkOut 
               .Execute
          End With
          dayCount = dayCount + 1
     Loop
     dayCount = 1
     rs.MoveNext
Loop

SQL statements in question:

/* strSqlSelect (note: G_SCHOOLYEAR returns the value 2) */
SELECT
    tbl_Routes.routeID
  , tbl_Routes.checkInTime  AS checkIn
  , tbl_Routes.checkOutTime AS checkOutFROM tbl_Routes
WHERE
    (
        (
            (
                tbl_Routes.schoolYearID
            )
            =G_SCHOOLYEAR()
        )
    )
;

/* strSqlInsert */
INSERT INTO tbl_Route_Times
    (
        routeID
      , dayID
      , checkIn
      , checkOut
    )
    VALUES
    (
        [pRoute]
      , [pDay]
      , [pIn]
      , [pOut]
    )
;

Welcome to SO! Please take the tour and read How to Ask, then improve question (edit). Show all sql statements formatted. Use db.Execute dbFailOnError. May reveal an index issue. — ComputerVersteher
Using either .Execute dbFailOnError or Gustav's DAO method below, it throws the error 3022 - index issues. In my mind it should be adding a new record on every loop, but that clearly isn't happening here. — JD2

Gustav Gustav · Accepted Answer · 2020-02-01T16:59:19

Use DAO for such tasks - much simpler and way faster:

Public Function InsertDays()

    Dim db As DAO.Database
    Dim rsSource As DAO.Recordset
    Dim rsTarget As DAO.Recordset

    Dim dayCount As Integer

    Set db = CurrentDb()
    Set rsSource = db.OpenRecordset("tbl_Routes")
    Set rsTarget = db.OpenRecordset("tbl_Route_Times")

    Do While Not rsSource.EOF
        Do While dayCount < 5
            dayCount = dayCount + 1
            With rsTarget
                .AddNew
                    !Rid.Value = rsSource!routeID.Value
                    !Day.Value = dayCount
                    !In.Value = rsSource!CheckIn.Value
                    !Out.Value = rsSource!CheckOut.Value
                .Update
            End With
        Loop
        dayCount = 0
        rsSource.MoveNext
    Loop
    rsTarget.Close
    rsSource.Close

End Function

Insert query not executing inside loop

2 Answers