OpenRecordset returns last record only

G

Greg

Running the following code in my database should return numerous records in
rstSub but rstSub.RecordCount is only ever = 1. The record returned is always
the last record in Tbl_SelectedEquipment that meets the criteria. Can anyone
explain why?
Dim sqlSub As String
sqlSub = "SELECT * FROM tbl_SelectedEquipment "
sqlSub = sqlSub & "WHERE lng_BookingID = " & lngOpenArgs
Set rstSub = db.OpenRecordset(sqlSub, dbOpenDynaset)
 
D

Douglas J. Steele

RecordCount is seldom (I was actually going to say never) correct unless
you've moved to the end of the recordset first.

Try a rstSub.MoveLast before you check rstSub.RecordCount. (Don't forget to
issue rstSub.MoveFirst before you go on to use the recordset!)
 
G

Greg

You got it! Thanks so much.

Douglas J. Steele said:
RecordCount is seldom (I was actually going to say never) correct unless
you've moved to the end of the recordset first.

Try a rstSub.MoveLast before you check rstSub.RecordCount. (Don't forget to
issue rstSub.MoveFirst before you go on to use the recordset!)
Click to expand...
 
K

Klatuu

When you open a recordset, the RecordCount property will almost always return
1 if there are any records at all in the recordset. A return of 0 usually
means there are no records in the recordset. Even testing EOF and BOF is not
100% reliable. Here is how I usually handle opening a recordset to ensure I
have data:

Set rstActual = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot,
dbReadOnly)
'Be sure there are records to process
If rstActual.RecordCount > 0 Then
rstActual.MoveLast
rstActual.MoveFirst
 
D

Dirk Goldgar

Klatuu said:
When you open a recordset, the RecordCount property will almost
always return 1 if there are any records at all in the recordset. A
return of 0 usually means there are no records in the recordset.
Even testing EOF and BOF is not 100% reliable.
Click to expand...

Klatuu -

Can you give me an example of when a RecordCount of 0 does *not* mean
the recordset is empty? Or one where (EOF = True And BOF = True)
doesn't mean it's empty? I ask because I believe these *are* both 100%
reliable, and if I'm wrong I'd like to know it.

It is certainly true that (RecordCount > 0) is not reliable, because
there are circumstances where RecordCount = -1, indicating that the
number of records is not yet known.
 
K

Klatuu

I believe your understanding to be correct, but since I have not done
extensive testing on it myself, I did not want to make an absolute statement.
I may not be remembering correctly, but I seem to reacall a situation in
AC97 where a test for EOF was failing, but the details elude me.

I do know that if the recordcount is undeterimed, it can return -1. That is
why the test for > 0. This method has never failed me (yet).
 
Top