Test for zero records returned from Query????

[Rick]

I'm opening Form "Properties" with data from Query "Search"

DoCmd.OpenForm "Properties", acNormal
Forms!Properties.RecordSource = "Search"

If the Record Count returned from the "Search" Query is zero I want to
close the "Properties" form and display a message box.

Can anyone help me with testing for zero records returned?

Thanks VERY much for any assistance you might offer.

Rick

 

[Douglas J. Steele]

I'd strongly recommend you rename your form. Properties is a reserved word,
and using it for your own purposes can lead to all sorts of problems. For a
good discussion on names to avoid in Access, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

To check whether a form has any recordset, you can always check the
RecordCount property of the form's RecordsetClone object:

Forms!NameOfForm.RecordsetClone.RecordCount

While the RecordCount property won't always give you the correct count if
there are many rows on the form, if it's zero, you know that there are no
rows in the form.

 

[Rick]

Thanks Doug,

I thought "Properties" was a reserved word but I haven't been having any
problems with its use. I agree, however, and have changed the form's name
to "Contacts".


The following line seems to work fine and is returning a zero when there are
no rows in the "Contacts" form.
Forms!Contacts.RecordsetClone.RecordCount

I had tried the following, which of course did not work:
Forms!Contacts.RecordCount

Can you briefly explain "RecordsetClone" and why it's needed in this
statement?

Thanks VERY much.

Rick

 

[Douglas J. Steele]

From the Help file:

You can use the RecordsetClone property to refer to a form's Recordset
object specified by the form's RecordSource property.

The RecordsetClone property setting is a copy of the underlying query or
table specified by the form's RecordSource property. If a form is based on a
query, for example, referring to the RecordsetClone property is the
equivalent of cloning a Recordset object by using the same query. If you
then apply a filter to the form, the Recordset object reflects the
filtering.

This property is available only by using Visual Basic and is read-only in
all views.

 

[Rick]

Thanks, but I had already read the help file text.

I just don't understand why cloning is necessary and why
"Forms!Contacts.RecordCount" doesn't work just as well as
"Forms!Contacts.RecordsetClone.RecordCount"

None-the-less the problem is solved whether I understand how or not.

Thanks,

Rick

 

[Richard Mueller [MVP]]

I'm guessing because I don't code in Access, but in general the RecordCount
property requires a certain cursor type. A forward cursor does not support
RecordCount for example. Perhaps the RecordsetClone has the proper cursor
type to support the RecordCount property.

Also, in some cases when you retrieve RecordCount, the cursor is left at the
end of the recordset and cannot be brought back to the beginning to read
rows. Maybe RecordsetClone leaves the cursor at the beginning of the
original recordset.

 

[Douglas J. Steele]

Forms don't have a RecordCount property, only Recordsets do.

Now, if your question was 'why "Forms!Contacts.Recordset.RecordCount"
doesn't work just as well as "Forms!Contacts.RecordsetClone.RecordCount"',
then I'd have to say I don't have an answer. <g>