2010 Access vba moving to different records in ADO

Question

I have a need to update a record either (n) records before or after the current record using ADO.

I have the code to open, read through all the selected records and close.

What I'm wondering is if there's a way to change the current record pointer. For example, I'd like to do something like this:

i=1
Do until rs.EOF
  If i > 5 then
    Field2=rs!Field1
    rs!CurrentRecordPointer = i-3
    'update Field2 in this record
    rs!CurrentRecordPointer = i+3
  End If
  i = i + 1
  rs.MoveNext
Loop

Is this possible, or will I need to use an array?

Answer 1

With a DAO object reference you can iterate through your records and update them.

Dim db As Database, myRecordset As Recordset
Set db = CurrentDb()

sqlString = "SELECT myfield FROM myTable"

Set myRecordset = db.OpenRecordset(sqlString, dbOpenDynaset, dbSeeChanges)

myRecordset.MoveFirst

Do While myRecordset.EOF = False
    myRecordset.Edit
    myRecordset.Fields("myfield ").Value = newValue
    myRecordset.Update
    myRecordset.MoveNext
Loop