Code produces error (Trying to iterate through rows in a Table)

[surferxx]

I get the error '3021' for this: Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

Heres the code:
Where am I making a mistake


Option Compare Database

Public Sub IterateRows()

Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim i As Integer

Set conn = CurrentProject.Connection
'conn.Open
rst.Open "401165_406282", conn, adOpenForwardOnly, adLockOptimistic, adCmdTable

rst.MoveFirst

Do Until rst.EOF
rst.Find "Field5 = 'FDC'", 1, adSearchForward
Debug.Print rst.Fields("Field5").Value
rst.MoveNext
Loop

rst.Close
conn.Close

End Sub

 

[pbaldy]

If all you want is the records that contain that specific value, why not open the recordset on an SQL string that returns only those records rather than the whole table? Opening the whole table to find certain records is inefficient.

 

[surferxx]

Well I want to run several Criteria on the same table and then based on the results of these criteria i want to insert records into rows in another table. So, I guess this is a better way instead of opening several recordsets based on several sql strings

 

[pbaldy]

Well you haven't said if it errors to start with or finds one or more first. From Help:

An unsuccessful search stops at the end of the Recordset if the value is adSearchForward

so I suspect the MoveNext would error at that point. In fact, the debug line might error first.

 

[Bat17]

try
Do While Not rst.EOF
rather than
Do Until rst.EOF


Peter

 

[Bat17]

An unsuccessful search stops at the end of the Recordset if the value is adSearchForward

so I suspect the MoveNext would error at that point. In fact, the debug line might error first.

Good point.
Debug.Print rst.Fields("Field5").Value will probably trigger the error if EOF

Well I want to run several Criteria on the same table

Using SQL you can apply lots of criteria in one hit.

Peter

 

[gemma-the-husky]

the movefirst will trigger an error if the dataset is empty

the movenext will always trigger an error if eof is true (ie after the last record)

and as paul says, the cursor will stop at the last record even if no matches which may give you an incorrect match

 

[surferxx]

the movefirst will trigger an error if the dataset is empty

the movenext will always trigger an error if eof is true (ie after the last record)

and as paul says, the cursor will stop at the last record even if no matches which may give you an incorrect match

Ok if I understand this right, the cursor stops at the last record and the rst.movenext statement sets EOF to True. So to avoid that I simply remove the move next statement? Or tell the cursor to go back to the first record? If so how?

 

[gemma-the-husky]

you need a movenext statement to step through the records

you can fix this line, though with

if not rst.eof then rst.MoveNext

 

[surferxx]

you need a movenext statement to step through the records

you can fix this line, though with

if not rst.eof then rst.MoveNext

Hey Gemma...that worked!...Thanks a lot! But why is it working even if I comment out the movenext statement???

 

[Bat17]

because the 'find' call steps you through the recordset when you call it each loop.

Peter

 

[gemma-the-husky]

looking at it again, the first statement is a searchforward, so i guess that does what it says on the tin, and searches forward from the last point.

in that case perhaps you don't want the movenext at all, or you might end up skippimg over records you really need