Solved: How to go back to the first record

[kbsudhir]

Hi All,

I am deleteing the rows from the recordset based on 8criterias uisng VBA.
I want to go back to the start of the record once I reach EOF how to do this.

Regards
Sudhir

[OBP]

If it is a Recordset you use
recordset.movefirst
To go to the last, to find an accurate count of the number of records use
recordset.movelast
for the record count use
recordset.recordcount
where recordset is the name of your recordset object variable.

[CreganTur]

In this example, rst is your recordset object:
If rst.EOF Then
rst.MoveFirst
End If
The above code looks to see if you are at EOF. If you are, then it moves back to the very first record in the recordset.

[Movian]

I would just mention though, be careful if you are using CreganTur's example within a loop that uses the EOF criteria. Otherwise you may put yourself into an infinite loop and have to use ctrl+break to stop it.

E.G.
While not MyRs.eof
//do somthing
if myrs.eof then
myrs.movefirst
end if
wend


That for example would be a BAD situation so just try to make sure you have an exit situation from your loop (If of course you are using a loop) :)

[kbsudhir]

Thanks you guys for all the help.

:bow: :bow: :bow:

Regards
Sudhir

[CreganTur]

Movian makes an excellent point... but I would stay away from the While...Wend loop structure. Instead, I would use a Do Until loop:



Do Until MyRs.eof
//do somthing
If myrs.eof Then
myrs.movefirst
End If
Loop

[Movian]

Some how i knew you were going to post that as soon as i had made my post :P

hehe i still need to get into the habit of using the until instead of the while

[CreganTur]

Some how i knew you were going to post that as soon as i had made my post :P

hehe i still need to get into the habit of using the until instead of the while

Yeah... I thought about that as I was posting it :p

It truly is a personal choice... I just don't like using While...Wend because it's outdated and only available for backwards compatability. Plus, the other loop structures are more flexible, especially when it comes to exiting early (and gracefully).

[OBP]

Personally I don't use EOF at all, I still used the original (out of fashion) For/Next loop based on the Recordcount.
But that is what comes from starting programming with BASIC in the 80s I suppose.

[Movian]

i use for Loops too, however eof is useful if you just want to cycle through a complete record set. instead of having to figure out how many records there are which would take an additional few lines of code

E.G.

myrs.movelast
myrs.movefirst
dim counter as integer

for counter = 0 to myrs.recordcount
//do somthing
next


vs

do until myrs.eof
//do somthing
loop

so that is definatly a change in code effiency (Specifically for this situation at least many other times the for loop is FAR more efficient)

but any way this is starting to get off topic now :)

[CreganTur]

I have heard of rare instances where looping through the record count can cause you to move into the EOF, which will cause an error. I've never seen this myself, so it may be a programming myth :dunno

I prefer working with EOF and BOF, instead of record count, because they require less coding. Also, if your recordset is massive, then EOF and BOF will save a little bit of processing time.