By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,964 Members | 1,810 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,964 IT Pros & Developers. It's quick & easy.

best way to test for an empty recordset?

P: n/a
rst.eof = true and rst.bof = true

or

rst.movelast
rst.recordcount = 0

?
Nov 13 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
The MoveLast will fail if there are no records.

In DAO, you can just use the RecordCount. It will be at least 1 if there are
records, and 0 if there are none.

In ADO, the RecordCount may be -1 (undefined?), depending on the kind of
recordset.

Either way you should be okay with:
If rst.BOF And rst.EOF Then

(Note that BOF and EOF are true/false values, so you don't need to compare
them to True to get a true/false result.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mike MacSween" <mi************************@btinternet.com> wrote in message
news:41*********************@news.aaisp.net.uk...
rst.eof = true and rst.bof = true

or

rst.movelast
rst.recordcount = 0

Nov 13 '05 #2

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
The MoveLast will fail if there are no records.
Of course. Silly me.
In DAO, you can just use the RecordCount. It will be at least 1 if there
are records, and 0 if there are none.

In ADO, the RecordCount may be -1 (undefined?), depending on the kind of
recordset.

Either way you should be okay with:
If rst.BOF And rst.EOF Then

(Note that BOF and EOF are true/false values, so you don't need to compare
them to True to get a true/false result.)


Of course. Double silly me

Thanks Allen
Nov 13 '05 #3

P: n/a
Hi Chuck.

According to the A2003 help, under RecordCount Property (ADO):
Use the RecordCount property to find out how many records are
in a Recordset object. The property returns -1 when ADO cannot
determine the number of records or if the provider or cursor type
does not support RecordCount.

My understanding of that statement is that the RecordCount is undetermined,
so I'm not sure I want to trust -1 to be "some". Perhaps I'm paranoid.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:kv********************************@4ax.com...

-1 = true (Yes, there are records, but no idea how many yet)?
That's the way I've always taken it....

On Mon, 29 Nov 2004 19:15:33 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:
The MoveLast will fail if there are no records.
In DAO, you can just use the RecordCount. It will be at least 1 if there
are
records, and 0 if there are none.
In ADO, the RecordCount may be -1 (undefined?), depending on the kind of
recordset.
Either way you should be okay with:
If rst.BOF And rst.EOF Then
(Note that BOF and EOF are true/false values, so you don't need to compare
them to True to get a true/false result.)

Nov 13 '05 #4

P: n/a
Allen Browne wrote:
Either way you should be okay with:
If rst.BOF And rst.EOF Then


I don't understand. Why test both? Isn't just testing one OK? In DAO,
what I do is test eof, ie,

Set rst = dbs.openrecordset(strSQL)

if rst.eof then

rst.close

else

'do recordset stuff

end if

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Nov 13 '05 #5

P: n/a
presumbably because just being at the eof isn't the same as the recordset
being empty.

"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:co**********@coranto.ucs.mun.ca...
Allen Browne wrote:
> Either way you should be okay with:
If rst.BOF And rst.EOF Then


I don't understand. Why test both? Isn't just testing one OK? In DAO,
what I do is test eof, ie,

Set rst = dbs.openrecordset(strSQL)

if rst.eof then

rst.close

else

'do recordset stuff

end if

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

Nov 13 '05 #6

P: n/a
Tim, that's probably fine straight after an OpenRecordset().

However, if the code is referring to an open recordset (e.g. the
RecordsetClone of a form, which could have been set by some other process at
some previous time), testing both is safer.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:co**********@coranto.ucs.mun.ca...
Allen Browne wrote:
> Either way you should be okay with:
If rst.BOF And rst.EOF Then


I don't understand. Why test both? Isn't just testing one OK? In DAO,
what I do is test eof, ie,

Set rst = dbs.openrecordset(strSQL)

if rst.eof then

rst.close

else

'do recordset stuff

end if

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

Nov 13 '05 #7

P: n/a
"Mike MacSween" <mi************************@btinternet.com> wrote in message news:<41*********************@news.aaisp.net.uk>.. .
rst.eof = true and rst.bof = true

or

rst.movelast
rst.recordcount = 0

?


If you're using Access you could always use the DCount function to
determine if rows returned are equal to zero.
E.G. If (DCount([ColumnStr],[TableStr],[CriteriaStr]) = 0) Then

Just an alternative, that might be sufficient. :)

Adam
Nov 13 '05 #8

P: n/a
Tim, if you are at the first record, and MovePrevious, you are not at BOF.
You are not at EOF. Testing BOF alone does not indicate there are no
records.

If you are at the last record, and MoveNext, you are now at EOF. You are not
at EOF. Testing EOF alone does not indicate there are no records.

If you are at both BOF and EOF, then there is no question: there are no
records. This is safe in all cases.

If you have just opened a recordset, then if either BOF or EOF is true, you
could correctly assume there are no records. However, this assumption is
invalid if you are testing a recordset that is already open, such as the
RecordsetClone of a form.

Therefore, the safe code that works in all cases is to test BOF and EOF.

Or (as in a previous reply), you will receive a RecordCount of at least 1 if
there are records in a DAO recordset.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:co**********@coranto.ucs.mun.ca...
Allen Browne wrote:
> Either way you should be okay with:
If rst.BOF And rst.EOF Then


I don't understand. Why test both? Isn't just testing one OK? In DAO,
what I do is test eof, ie,

Set rst = dbs.openrecordset(strSQL)

if rst.eof then

rst.close

else

'do recordset stuff

end if

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

Nov 13 '05 #9

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
Tim, if you are at the first record, and MovePrevious, you are not at BOF.
That's a typo Allen, yes? Not should be now?
Or (as in a previous reply), you will receive a RecordCount of at least 1
if there are records in a DAO recordset.


rst.MoveLast then rst.RecordCount is the way to get a reliable RecordCount
yes?

Could anything run so fast that rst = db.openrecordset(...)

rst.recordcount would have time to even count 1 record?

Probably not.
Nov 13 '05 #10

P: n/a
Mike MacSween wrote:
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
Tim, if you are at the first record, and MovePrevious, you are not at BOF.

That's a typo Allen, yes? Not should be now?


PMFJI but I read it as "you are not at EOF". That makes the contrast to
the other statement.
Could anything run so fast that rst = db.openrecordset(...)

rst.recordcount would have time to even count 1 record?

Probably not.


I get the feeling 1 is the initialized value for the property. Only
after a conclusion is drawn (no records) or navigation is performed, the
actual value gets assigned.

Just a guess, though.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Nov 13 '05 #11

P: n/a
Allen Browne wrote:
If you are at both BOF and EOF, then there is no question: there are no
records. This is safe in all cases.

If you have just opened a recordset, then if either BOF or EOF is true, you


Thank you allen, that's something to remember. Most of the time, my
apps are such that I test for eof right after a recordset has been
opened, before any move whatevers are performed. I've never noticed a
problem, but now that you folks have pointed out some of the
considerations, i think I'll go with testing both from now on.

In general, when I'm doing recordset operations, I rarely (and can't
remember ever) do a delete. When I play with recordsetclone, I try very
hard to remember to "reset" by doing a .movefirst so that other
references to the rsclone don't start where I last left off.

Thanks.
--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Nov 13 '05 #12

P: n/a
Yep: typo. MovePrevious from first record takes you to BOF.

Provided you use DAO, Mike, then the RecordCount will be at least 1 if there
are records, 0 if there are not. I personally use:
If rst.RecordCount > 0
and it never fails, so your suggestion is safe.
Just bear in mind that this applies to DAO only.

As explained earlier, ADO recordsets can give you a RecordCount of -1 for
undefined. I believe that's why some developers prefer:
If rs.BOF And rs.EOF
since that works with both DAO and ADO.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mike MacSween" <mi************************@btinternet.com> wrote in message
news:41*********************@news.aaisp.net.uk...
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
Tim, if you are at the first record, and MovePrevious, you are not at
BOF.


That's a typo Allen, yes? Not should be now?
Or (as in a previous reply), you will receive a RecordCount of at least 1
if there are records in a DAO recordset.


rst.MoveLast then rst.RecordCount is the way to get a reliable
RecordCount yes?

Could anything run so fast that rst = db.openrecordset(...)

rst.recordcount would have time to even count 1 record?

Probably not.

Nov 13 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.