Item not in collection when using field collection

[morphus]

Greetings, need assistance understanding what I am doing to generate the above error using Access 2007.

My goal is to cycle thru field collection of rstA which contains 79 fields. For each record in rstA, I want to create a new record in rstB using rstA.fields 0-19 plus 1 field beginning at field 20. Ideally, I should create 60 rstB records for each rstA record.

Fields 0-19 of rstB structure are the same as rstA. Just need to update rstB field 20 while cycling from rstA fields 20-79.

I have been stumped for several days trying to work thru this but unable to resolve. Any assistance would greatly be appreciated. Also, I understand the complaint is likely from rstB being nested under rstA's "with" statement. Have tried nested "with" statements without success.


code snippet

With rstA
    Do Until rstA.EOF
        For y = 20 To rstA.Fields.Count - 1
                For i = 0 To 19
                    rstB.AddNew
                    rstB.Fields(i) = rstA.Fields(i)
                Next
        rstB.Fields(20) = rstA.Fields(y)
        rstB.Update
        Next
    rstA.MoveNext
    Loop
End With

 

[LPurvis]

Hi

Your with block seems fairly redundant anyway - you're making explicit references to the recordsets each time anyway.

If I'm understanding your requirements - you're attempting to add the new row too often.
This assumes that the tables and recordsets are appropriate to the definition as listed (i.e. there are the correct number of fields to hit allowing for a zero based ordinal position - for example Field 20 would be the 21st field in the recordset rstB, you've already identified this from 0 to 19.)

With rstA
    Do Until .EOF
        For y = 20 To .Fields.Count - 1
            rstB.AddNew
            For i = 0 To 19
                rstB.Fields(i) = .Fields(i)
            Next
            rstB.Fields(20) = .Fields(y)
            rstB.Update
        Next
        .MoveNext
    Loop
End With

Cheers

 

[morphus]

Thanks for response. Given that its been nearly 10 years since last VBA project, I am surprised I have memories of anything.

 

[LPurvis]

Then you're sorted?
Glad to hear it.
Welcome to AWF.

 

[morphus]

Yes, I am. I am deeply grateful for the help.