How to get query RowCount from code?

[DaveKristof]

I'm looking for a way to open a form and have it automatically check
rowcounts for multiple queries (which means you can't just link the
query to the form) in the code, but none of the methods I've found
have worked.
I'm using MS Access 2000.

I've tried copying/pasting some ADO and DAO code I found while
searching to find a fix, but none seemed to work or even come remotely
close to working and I know nothing about either.

Basically I'm trying to do this:

----------------------------------------
Private Sub Form_Load()

if Query1.RowCount > 0
<do some stuff>
end if

if Query2.RowCount > 0
<do some stuff>
end if

if Query3.RowCount > 0
<do some stuff>
end if

if Query4.RowCount > 0
<do some stuff>
end if

end sub

----------------------------------------

The only way I can figure out how to do this is to create 1 form for
each query and link the query to it, then I can use docmd.gotorecord
aclast and use CurrentRecord. But then in my mainform the Form_Load
sub I have to open 4 new forms and then tell them to modify a label or
text box on my main form to the RowCount value. Then back in my
mainform, I can check the values of the labels/textboxes and use those
values in my if statements. This is very inconvenient and very
inefficient, there must be an easy way to just get RowCount values
from any query!

Thanks in advance for any help!

 

[Albert D. Kallal]

Try: dcount("*","Query1") > 0 then

if dcount("*",Query2) > 0 then
<do some stuff>
end if

etc....

 

[DaveKristof]

THANKS!
This is exactly what I was looking for, it works perfectly. Hard to
find stuff like this when you don't know what to search for.

 

[James A. Fortune]

THANKS!
This is exactly what I was looking for, it works perfectly. Hard to
find stuff like this when you don't know what to search for.

Here's some additional information of dubious value:

In:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/a67a1f43277864b0

I said:

I think you can even use:

Result = DLookup("Count(*)", "tblCustomer", "optional Where Clause")


My guess is that this method works for almost all of the domain
aggregate functions.

Result = DLookup("Avg(FieldToAvg)", "tblX")

versus

Result = DAvg("FieldToAvg", "tblX")

The A97 help info for "Domain Aggregate Functions" states that SQL
aggregate functions are generally more efficient than domain aggregate
functions but I have not done speed comparisons since I only use domain
aggregate functions when I'm in a pinch. When I get time I usually just
use SQL.

James A. Fortune
(e-mail address removed)