How to determine an empty recordsource result? (1 Viewer)

[Lierduh]

A simple question for you.

One of my form is based on a SELECT statement. I wan to know if the recordsource returns empty result at time the form is opened. Is there an easy way to do this?

If I was to test if the result is empty? do I do at the form openning event?

Thank you

 

[Oldsoftboss]

In the On Load event

If RecordsetClone.RecordCount = 0 Then

Msgbox "What ever you like"

'or.. DoCmd.Close

End If

HTH
Dave

 

[Calvin]

If you're using Access97 for the .RecordCount to be reliable you have to use .MoveLast then check/capture your record count. I believe this was fixed in 2k and later.

What I use and has never failed me is checking for BOF and EOF, because if your recordset is empty your current location in memeory will be both BOF and EOF at the same time.
Try this:

    if rst.BOF And rst.EOF then {do something}
OR
    if NOT rst.BOF And NOT rst.EOF then {do something}

If you are still using 97 and if you try to .MoveLast and your rst is empty you generate an error because, and even in later versions, you cannot navigate an empty rst.

 

[Rich]

RecordsetClone.RecordCount has never proved unreliable in 97 for me, what problem is there supposed to be and is there any documentation on this bug?

 

[Calvin]

You made me look, so here it is...

Rich,
My original source was a class I took and the two books I have for Developing Apps in Access97 both mention it, but if you want a source you will probably believe, see below.

I'm not aware of specific issues with RecordsetClone, just .RecordCount has a variety of issues with recordsets or tables or queries, text files, with Dynaset, Snapshot or whatever open methods, and I do stand corrected, there are still some issues with it in Access 2000. Please See Below.

Access97

CAUSE
For recordsets and snapshots, Microsoft Access does not automatically return the number of records that exist in the recordset. Rather, it returns the number of records accessed.
RESOLUTION
To determine the exact number of records in a recordset or snapshot, use the MoveLast method before checking the RecordCount property.
STATUS
This behavior is by design.

http://support.microsoft.com/default.aspx?scid=kb;en-us;105976

CAUSE
For Dynasets and Snapshots, the RecordCount property does not automatically return the number of records that exist in the recordset. RecordCount returns the number of records accessed. If you don't do a MoveLast method immediately before checking the record count, you will get an incorrect, smaller count.
RESOLUTION
To determine the number of records in a Dynaset or Snapshot, use the MoveLast method before checking the RecordCount property. This requirement is by design.

NOTE: If you add or delete records to a table within a transaction, and then roll back the transaction, the value of the RecordCount property is not adjusted accordingly.
STATUS
This behavior is by design because otherwise, Visual Basic would have to do an implicit MoveLast. This would be very slow with large record sets and especially with remote databases, so the decision is left up to the programmer.

http://support.microsoft.com/default.aspx?scid=kb;en-us;109053


Access 2000

http://support.microsoft.com/default.aspx?scid=kb;en-us;207652
http://support.microsoft.com/default.aspx?scid=kb;en-us;194973


There are lots more here: Click Here