VBA: RecordSet values overwritten by MoveNext

Question

I've encountered a strange problem during some work with VBA and a DAO.Recordset. I'm currently looping throgh the recordset and save the corresponding fields for earch iteration in an array. But every time I call ".MoveNext" due to the while-loop, the information stored in the "rs.Fields.Field(i).Value" is being overwritten, thus the fields are unusable. I've figured this out during debugging.

Some relevant code:

If Not rs Is Nothing Then
      If rs.RecordCount > 0 Then
        With rs
            While Not .EOF
                ReDim Preserve fieldSet(0 To i + 1) As DAO.Fields
                Set fieldSet(i) = rs.Fields
                i = i + 1 ' the values are still intact at this point
                .MoveNext ' here's where there's only "No current record" stored inside the value-field
            Wend
        End With
      End If

Hopefully somebody has an idea what causes this. Thanks in advance.

Since you will be moving to the next record the fields will be reset to the next record.. What do you mean by this? rs.Fields.Field(i).Value? or do you mean rs.Fields(i).Value? — bonCodigo
I don't think that's going to work. All you're doing is copying a reference to rs.fields into your array. So, all members of the array will point to the same thing. — Tim Williams

bonCodigo bonCodigo · Accepted Answer · 2013-01-22T14:26:44

Try running another loop within for the field/column count in each record:

Do While Not rs.EOF
   For j = 0 to rs.Fields.Count
     Set fieldSet(j) = rs.Fields.Field(j).Value 
     '-- do stuff
   Next j
   rs.MoveNext
Loop

VBA: RecordSet values overwritten by MoveNext

1 Answers