Need VBA Help - item not found in this collection error

[nbwest76]

I need help with the following code. The code is pulling records from table "Questions" and looping through the columns labeled "Questions #1" - #10. It works fine but only for the first ID number, I get an item not found error once it reaches the last column ("Question #10") for the first ID number, so basically my loop is not moving to the next ID number.

Option Compare Database
Option Explicit

Sub SomeProcedure()
    Dim db As DAO.Database, recIn As DAO.Recordset, recOut As DAO.Recordset, i As Integer
       

    Set db = CurrentDb()
    Set recIn = db.OpenRecordset("Questions", dbOpenDynaset, dbReadOnly)
    Set recOut = db.OpenRecordset("Questions2", dbOpenDynaset, dbEditAdd)

    With recIn
        .MoveFirst
        Do
            For i = 0 To .Fields.Count
            If Left(.Fields(i).Name, 8) = "Question" Then
                    recOut.AddNew
                        recOut.Fields("Loan Number") = recIn.Fields("Loan Number")
                        recOut.Fields("Total Questions") = recIn.Fields(i)
                    recOut.Update
                    End If
            Next i
         .MoveNext
        Loop Until .EOF
    End With
    recIn.Close
    recOut.Close
    db.Close
    
        
End Sub

[twinnyfo]

nbwest76,

First, please use the Code tags when posting your VBA.

Second, your variable i will cycle from 1 to the number of fields. however, fields are numbered from 0 to (n-1). So if you have 12 fields, they are numbered from 0-11. It will not be able to find field #12.

Hope this helps.

[nbwest76]

twinnyfo, it finds all of the fields correctly. My problem is after it finds all of the fields for the first ID number it fails to move to the next ID number.

[twinnyfo]

Try changing line 15 in your code to:

For i = 0 To .Fields.Count - 1

This is the problem. If you have 12 fields, they are numbered from 0 to 11, not 0 to 12.

Hope this helps.

[nbwest76]

I was going crazy trying to figure this out! Thank you! It worked perfectly.

[twinnyfo]

Glad I could help. I'm sure I'll lean on you someday, too!