Jump to specific record in recordset

[Pendragon]

Access03/WinXP

I have two recordsets - rs & rsCurrent - which have a one-to-many
relationship on CompanyID. I am programming a process to update an export
table and need to loop through both tables in order to update specific field
information for each record. Each recordset is ordered by an index, but I
don't want to make the assumption that both recordsets will always be ordered
properly (why, I don't know - is this just paranoia??) You will see that in
the outer Do Loop, I always return to the first record of the inner recordset
just to be sure that in looping through the inner recordset, no matching
records are missed.

This process runs on up to 4000 records depending on the time of the year,
and the field list in the For...Next loop circulates through about 30 fields.
In running my test, the process takes about 4-5 seconds.

I'm interested in suggestions for maximizing efficiency. I did think about
perhaps setting rsCurrent to filter the records based on rs("CompanyID") on
each iteration of the outer Do Loop - would this be a better option (see
second set of code)?

Thank you for any suggestions or critiques you may have.

rs.MoveFirst
rsCurrent.MoveFirst

Do While Not rs.EOF
rsCurrent.MoveFirst
Do While Not rsCurrent.EOF
If rsCurrent("CompanyID") = rs("CompanyID") Then
For Each fld In tdf.Fields
If fld.Name = rsCurrent("Placement") Then
With rs
.Edit
rs(fld.Name) = StrConv(rsCurrent("NewRenew"),
vbUpperCase) & "-" & rsCurrent("PartNumber")
.Update
End With
End If
Next fld
rsCurrent.MoveNext
Else
rsCurrent.MoveNext
End If
Loop
rs.MoveNext
Loop

****
The other option:

rs.MoveFirst

Do While Not rs.EOF
Set rsCurrent = MyDB.OpenRecordset("SELECT * FROM qryOrderProductsBase
WHERE qryOrderProductBase.CompanyID = " & rs("CompanyID"))
rsCurrent.MoveFirst
Do While Not rsCurrent.EOF
For Each fld In tdf.Fields
If fld.Name = rsCurrent("Placement") Then
With rs
.Edit
rs(fld.Name) = StrConv(rsCurrent("NewRenew"),
vbUpperCase) & "-" & rsCurrent("PartNumber")
.Update
End With
End If
Next fld
rsCurrent.MoveNext
Loop
rs.MoveNext
set rsCurrent = nothing
Loop

 

[Dennis]

Why is the order important to you? Normally, one would simply spin through
all the records found via a SELECT, and do whatever is necessary. Order is
normally only an issue when one is creating a report or query, and needs
records found to show in a very specific sort order.