For Each...Next Help

[VBAmateur]

I want to try to perform a function for each record that is in a recordset. SO say I have
set tb = db.openrecordset(Blue)
then I want to say for each record
call this function
then do the next record
and on and on


How to?

[Iain17]

I have never tried a for each next loop with a recordset but this achieves the same goal.

  Dim i As Integer
  
  'go to the first record
  rsAllRecords.MoveFirst

  For i = 0 To rsAllRecords.RecordCount - 1
    'call your function here
    rsAllRecords.MoveNext
  Next i

[Edited by Iain17 on 04-26-2000 at 02:05 PM]

Hello,

The solution Iain17 proposes might work if your recordset supports the RecordCount property. However, this is not always the case, because it depends on how you open your recordset.
Besides, using the .RecordCount property is considered "evil" because of performance.

You're better off using the EOF property of a recordset like this:

do while not rsAllRecords.EOF
' Call your function(s) here
rsAllRecords.MoveNext
loop

Hope this helps.

Imar

[dcarlson]

If your recordset is very large then the recordcount method presented by lain17 will be faster because the for next doesn't check for EOF every loop. But if your recordset is rather small it's better to use the EOF method.

I hope this is what you need

Dim fld As Field
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase("C:\Program Files\DevStudio\VB\Nwind.mdb")
Set rs = db.OpenRecordset("select * from shippers")

rs.MoveFirst

'you have to do it to be sure that rs is populated

For Each fld In rs.Fields
List1.AddItem rs(0) & " " & rs(1)
rs.MoveNext
Next

[Frans C]

I have to agree with Imar. His approach is the only one that will work under all circumstances.

What dcarlson says is just not correct. Accessing recordcount in each loop will cause much more overhead then accessing eof. And eof will work every time, while recordcount doesn't.

HelenZak makes a mistake also. He (or she) loops through the fields in a record, but in each loop moves a record ahead. So if the recordset has 3 fields, only 3 records are fetched. If there are less records then fields, an error is generated.

I am not complitly agry with Frans C
Itis true I mist one line of code

For Each fld In rs.Fields
List1.AddItem rs(2) & " " & " " & rs(3) & " "
rs.MoveNext
'this is the line i mist
If rs.EOF Then Exit For
Next
other then thet it works fine

[dcarlson]

Sorry, for the RecordCount to be faster you need get the recordcount first such as:

Dim i As Integer
Dim intRecCount as Integer

'go to the first record

intRecCount = rsAllRecords.RecordCount - 1

rsAllRecords.MoveFirst

For i = 0 To intRecCount
'call your function here
rsAllRecords.MoveNext
Next i

I never agree to use recordcount it is not rationale
but FOR EACH can be use with a better performance

Hello,

Whether or not you can / want to use the RecordCount property depends on the situation.
Most of the times you will we using a recordset in which you only move forwards (that is, only use rsMyData.MoveNext).
Examples: Filling lists, showing news messages, forum threads etc. In this case you don't need a dynamic recordset so you open it using the adForwardOnly parameter (assuming you use ADO). With an adForwardOnly Recordset, you can't use the RecordCount property. The reason for this is that RecordCount actually moves to the last record to get a count.
After that, you won't be able to start all over again and loop through the recordset.
So the only option left then, is to use a Cursortype like adOpenDynamic to dynamically loop through the recorset back and forth.
This, however, is a very expensive operation. An adOpenForwardOnly is way faster than a dynamic cursor, especially on large recordsets.
So, although you might be tempted to use the RecordCount property, in 90+ percent of the cases, you are probably better off using the .EOF property.

Imar

[Edited by Imar on 04-26-2000 at 11:55 PM]

[dcarlson]

I totally agree that it depends on situation and the EOF method is probably 90% of the time more efficient, but if you have a large non-forwardonly recordset and need to loop through them rulling out the RecordCount method is obsured.