Scanning through DAO.Recordset

[Guest]

Hi,

I was wondering if there is a more efficient way to scan through
DAO.Recordsets. I typically use RecordCount in a for loop, but before I use
it I need to MoveLast and then MoveFirst. Example:

Dim RS as DAO.Recordset
Dime x as Integer

Set RS = CurrentDb.OpenRecordset("SELECT * FROM [Main Table] WHERE
[ProductType] = '" & MC & "'", dbOpenDynaset, dbSeeChanges)

RS.MoveLast
RS.MoveFirst

For x = 1 To RS.RecordCount
'............
'Go through records.
'............
RS.MoveNext
Next x

Is there anyway I can avoid using MoveLast and MoveFirst?
I tried coming up with using an EACH in the for loop but was not successful.

 

[Rob Oldfield]

rs.movefirst
do while not rs.eof
'do whatever
rs.movenext
loop

 

[PC Datasheet]

For your loop, use:
Do Until RS.EOF
........
Next

You don't need to count the records first for this loop.

 

[mmarch-at-direct-dot-ca]

rs.movefirst
do while not rs.eof
'do whatever
rs.movenext
loop

I have been using:

Set Rs = ...
With Rs
While not .EOF
'do whatever
.MoveNext
Wend
End With

Is it not true that Rs will always start at the first record after a Set?

Hi,

I was wondering if there is a more efficient way to scan through
DAO.Recordsets. I typically use RecordCount in a for loop, but before I use
it I need to MoveLast and then MoveFirst. Example:

Dim RS as DAO.Recordset
Dime x as Integer

Set RS = CurrentDb.OpenRecordset("SELECT * FROM [Main Table] WHERE
[ProductType] = '" & MC & "'", dbOpenDynaset, dbSeeChanges)

RS.MoveLast
RS.MoveFirst

For x = 1 To RS.RecordCount
'............
'Go through records.
'............
RS.MoveNext
Next x

Is there anyway I can avoid using MoveLast and MoveFirst?
I tried coming up with using an EACH in the for loop but was not successful.

 

[PC Datasheet]

Yes!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

rs.movefirst
do while not rs.eof
'do whatever
rs.movenext
loop

I have been using:

Set Rs = ...
With Rs
While not .EOF
'do whatever
.MoveNext
Wend
End With

Is it not true that Rs will always start at the first record after a Set?

Hi,

I was wondering if there is a more efficient way to scan through
DAO.Recordsets. I typically use RecordCount in a for loop, but before

I
use

it I need to MoveLast and then MoveFirst. Example:

Dim RS as DAO.Recordset
Dime x as Integer

Set RS = CurrentDb.OpenRecordset("SELECT * FROM [Main Table] WHERE
[ProductType] = '" & MC & "'", dbOpenDynaset, dbSeeChanges)

RS.MoveLast
RS.MoveFirst

For x = 1 To RS.RecordCount
'............
'Go through records.
'............
RS.MoveNext
Next x

Is there anyway I can avoid using MoveLast and MoveFirst?
I tried coming up with using an EACH in the for loop but was not successful.

 

[Brendan Reynolds]

As long as there are records to be returned, yes, the recordset will be
positioned at the first record. If there are no records, both .BOF and .EOF
will be True. The first example below would raise an error when it attempted
to call the .MoveFirst method on an empty recordset before testing the .EOF
property, while the second example would not raise any error, the code
within the loop would not be executed (because .EOF would be True).

Sometimes, doing nothing when the recordset is empty is what you want. Other
times, you'll want to check whether the recordset returned any records by
testing either for .RecordCount <> 0 or (.BOF and .EOF) = True.

--
Brendan Reynolds (MVP)

rs.movefirst
do while not rs.eof
'do whatever
rs.movenext
loop

I have been using:

Set Rs = ...
With Rs
While not .EOF
'do whatever
.MoveNext
Wend
End With

Is it not true that Rs will always start at the first record after a Set?

Hi,

I was wondering if there is a more efficient way to scan through
DAO.Recordsets. I typically use RecordCount in a for loop, but before I use
it I need to MoveLast and then MoveFirst. Example:

Dim RS as DAO.Recordset
Dime x as Integer

Set RS = CurrentDb.OpenRecordset("SELECT * FROM [Main Table] WHERE
[ProductType] = '" & MC & "'", dbOpenDynaset, dbSeeChanges)

RS.MoveLast
RS.MoveFirst

For x = 1 To RS.RecordCount
'............
'Go through records.
'............
RS.MoveNext
Next x

Is there anyway I can avoid using MoveLast and MoveFirst?
I tried coming up with using an EACH in the for loop but was not successful.