EOF loop

[Harris@Z]

Hi, hope someone can assist.
Going in circles with this one.
I would like in an EOF loop, for the first record to trigger one string and the remaining records in the loop to trigger another.

Do While Not rs.EOF

 '1st record - do this
 '2nd and subsequent records - do that

rs.MoveNext
Loop

Can anyone assist in how to do this?

 

[theDBguy]

Maybe you could either process the first record out of the loop or create a flag to process the rest differently.

 

[Harris@Z]

Thanks, this is what I am struggling to fathom out

 

[Harris@Z]

Aah! Managed to solve the puzzle.
Had used a loop within a loop.

N = 0
Do While Not rs.EOF
 
    If N = 0 Then
      'Do this
    Else
      'Do this
    End if
    
N = N + 1     

rs.MoveNext
Loop

 

[theDBguy]

Glad to hear you got it sorted out. Good luck with your project.

 

[MajP]

If the redcordset is a dynaset or snapshot then no need for the counter just check the absoluteposition

if rs.absoluteposition = 0 then ' First record

 

[The_Doc_Man]

By default, the first record is opened on a DAO recordset. At that time, the .BOF (=Beginning of File) property would be true. You could just have an IF RS.BOF THEN (first record stuff) ELSE (later record stuff).

Note that if you opened the recordset abnormally or did something to move to a different record, .BOF is not necessarily true. But the plain vanilla method of opening the recordset should make this first/later record decision trivial via .BOF true/false.

 

[Pat Hartman]

I know this is trivial and overly picky but having once been in the position of having to care about the details (for the sake of efficiency) -- don't use an If inside a loop when the logic can exist outside of the loop.

Open the recordset.
Do the first record stuff
Start the loop
no condition necessary.

 

[The_Doc_Man]

Pat's post reminded me of something. She is absolutely NOT WRONG on her advice. If you have 10,000 records, you will do that IF statement 10,000 times, only 1 of which will choose a different path than the other 9,999 steps of the loop. VBA is an interpreted language, not an executed language. If you have 10,000 records, you will execute that IF test 10,000 times to only catch one record of the recordset. For interpreted code, that is a high overhead.

IF you have only a few dozen or a few hundred records, the repeated test is not as big an issue. The more records you have, the worse it gets.

 

[Harris@Z]

Thanks everyone for the excellent advice! This is what I was hoping to get help resolving my issue, and your advise is far better than my initial solution!

 

[Solo712]

I know this is trivial and overly picky but
t having once been in the position of having to care about the details (for the sake of efficiency) -- don't use an If inside a loop when the logic can exist outside of the loop.

Open the recordset.
Do the first record stuff
Start the loop
no condition necessary.

I don't think this is trivial or picky. It's a question of acquiring the habit of writing efficient code. The inner loop is not just inefficient, but a totally unnecessary code. You know that the inner loop evaluates positively only once, and on the first hit. So, going through the test is silly, let alone going through it x-times.

So, it is better to do like this:

If not rs.EOF then   ' You only need to test that the recordset is not empty
   rs.MoveFirst
   ' do your stuff for first record
   rs.MoveNext
End If
Do While not rs.EOF
   ' do your stuff for the following records
   rs.MoveNext
Loop

Best,
Jiri