.BOF AND .EOF vs .RecordCount to test existing of record

  • Thread starter Thread starter Albert
  • Start date Start date
A

Albert

Hello All,

1) Early, I know I can use statement
If RS.BOF And RS.EOF Then Msgbox "No record in recordset."
to check whether or not RS contain record.

But if I test with RecordsetClone
set RS = Me.RecordsetClone
If RS.BOF And RS.EOF Then Msgbox "No record in recordset."
then this message box is displayed eventhough RS contains records.

With RS.BOF And RS.EOF , Doesn't it apply to RecordsetClone ?

2) I solve this problem by use If RS.RecordCount > 0 instead. So, Can I
replace RS.BOF And RS.EOF by .RecordCount for every case ? Have any
exception in case that RS contains record(s) but .RecordCount return 0 ?

Thanks in advance
 
Albert said:
1) Early, I know I can use statement
If RS.BOF And RS.EOF Then Msgbox "No record in recordset."
to check whether or not RS contain record.

But if I test with RecordsetClone
set RS = Me.RecordsetClone
If RS.BOF And RS.EOF Then Msgbox "No record in recordset."
then this message box is displayed eventhough RS contains records.

With RS.BOF And RS.EOF , Doesn't it apply to RecordsetClone ?

2) I solve this problem by use If RS.RecordCount > 0 instead. So, Can I
replace RS.BOF And RS.EOF by .RecordCount for every case ? Have any
exception in case that RS contains record(s) but .RecordCount return 0 ?
Click to expand...

rs.RecordCount > 0 is always reliable.

I can't imagine how both EOF and BOF could be true for
RecordsetClone of a form with data.
 
I agree Marsh.
Also, I never understood why you had to test for both.
If a recordset is empty then rs.BOF=True is good enough.
If there are records then the rs is always positioned on the first row.
(MS states you should not rely on this behavior of data in first row but it
has always been this way.)
 
Joe said:
I agree Marsh.
Also, I never understood why you had to test for both.
If a recordset is empty then rs.BOF=True is good enough.
If there are records then the rs is always positioned on the first row.
(MS states you should not rely on this behavior of data in first row but it
has always been this way.)
Click to expand...


I agree too, Joe. Even if we shouldn't rely on a freshly
opened recordset being positioned on the first record, I can
not see any logical reason why it would be positioned either
before the first record or after the last record.

Regardless of all that bleeping confussion, the RecordCount
of a nonempty recordset can never be zero, so that's what I
always check.
 
The two-way test is safer IMO.

There are things that the code could do, to make rs.eof True on a non-empty
recordset. But there is nothing that the code can do, to make eof and bof
both< true on a non-empty recordset. So, the eof-only test implies an
Click to expand...
assumption about what has been done (or not done) with the recordset before
the test. The 2-way test makes no such assumption.

TC


Joe Fallon said:
I agree Marsh.
Also, I never understood why you had to test for both.
If a recordset is empty then rs.BOF=True is good enough.
If there are records then the rs is always positioned on the first row.
(MS states you should not rely on this behavior of data in first row but it
has always been this way.)
Click to expand...
 
Hi, The following is excerpt from BOF, EOF Property Help File :

"...If you open a Recordset object containing no records, the BOF and EOF
properties are set to True, and the Recordset object's RecordCount property
setting is 0. ..."

and here from DAO Recordset Object Help File (ADO Recordset Object Help File
also same) :

"... When you create a Recordset object, the current record is positioned to
the first record if there are any records. If there are no records, the
RecordCount property setting is 0, and the BOF and EOF property settings are
True. ..."

From both informations , Does it mean we have to check both BOF and EOF ? (I
can't remember but it seems that on Access old version (2 or 95), if test
only one condition for empty recordset, it sometimes not correct.)

In case of RecordsetClone, AFAIK there is no current record until set
Bookmark or MoveFirst/Last. Opened recordset is different, if non-empty
recordset, it always position to some (first) record.
 
Marshall Barton said:
I agree too, Joe. Even if we shouldn't rely on a freshly
opened recordset being positioned on the first record, I can
not see any logical reason why it would be positioned either
before the first record or after the last record.

Regardless of all that bleeping confussion, the RecordCount
of a nonempty recordset can never be zero, so that's what I
always check.
Click to expand...

I believe the dual test of .BOF and .EOF is based on the idea that you
may be dealing with a recordset that you didn't just open, that you were
effectively handed out of nowhere. In that case, you don't know where
the current-record pointer may be positioned, and it could easily be at
either BOF or EOF even though the recordset is not empty. It cannot,
however, be at *both* BOF and EOF unless the recordset contains no
records at all.

I personally have always relied on a freshly opened recordset to be
positioned at the first record if there is one, and it is my deep
conviction that we can rely on this. So I have no qualms about writing

Set rs = CurrentDb.OpenRecordset( ... )
If rs.EOF Then
' there are no records ...
End If

I don't know why the MS documentation doesn't advocate a test for
RecordCount, except that for some non-Jet recordsets you get an initial
RecordCount of -1 if there *are* records -- I'm not sure if you get -1
if there are no records or not. I wonder also if the .RecordCount
property existed in early versions of DAO.
 
Marshall Barton said:
Well, yes TC, if you do not know what's gone before with the
recordset. I thought we were discussing a freshly opened
recordset, in which case either EOF or BOF wil do. I still
prefer the RecordCount > 0 approach though, but if it's your
code, then you get to make the call and do it your way. ;-)
Click to expand...


True about the freshly opened bit. But the rs might be passed to a
function - which does not know what hapenned before.

Cheers,
TC
(off for the day)
 
TC said:
The two-way test is safer IMO.

There are things that the code could do, to make rs.eof True on a non-empty
recordset. But there is nothing that the code can do, to make eof and bof
assumption about what has been done (or not done) with the recordset before
the test. The 2-way test makes no such assumption.
Click to expand...

Well, yes TC, if you do not know what's gone before with the
recordset. I thought we were discussing a freshly opened
recordset, in which case either EOF or BOF wil do. I still
prefer the RecordCount > 0 approach though, but if it's your
code, then you get to make the call and do it your way. ;-)
 
TC and Marsh,
I tend to agree with Marsh - but that is mostly because I hadn't considered
TC's views before.
I can see the value in being safer now. That doesn't mean I will change my
code, but it does mean I will think twice about the "state" of the rs before
manipulating it. (We were discussing freshly opened recordsets.)
 
Back
Top