Counting Query

[Dannybe2]

How do I write some code to count the number of records in a query. I have a program with records that gradually decreases, and I have a button that goes to a random record - the problem is that as the number of records decreases, the random search picks out records that have disappeared from the query and it crashes.

How do I count how many records there are in a query at any time?

Thanks,
Dan

 

[JoanneM]

hello
You do this by making a recordset of the query and using the recordcount Property


so in code using DAO
nb make sure office DAO Library is referenced otherwise you will get errors
Nb compile and check this as I typed it directly.



function QryRecCount() as integer
dim db as database
set db = currentdb()
dim QRS as recordset
set QRS = mydb.openrecordset("Queryname", db_open_dynaset)

with QRS
if .eof = true and .bof = true then
QryRecCount = 0
exit function
else
.movelast
qryRecCount = .RecordCount
end if
end with

end function

if you need and ADO Version let me know

regards

Jo

 

[Junior1544]

even easier is to use dcount...

Variable or field = DCount("field to count", "table or query&quot

this should work much easier then opening a recordset and doing every thing that way... though that will work, it'll take more cpu cycles...

--Junior

p.s. you can even incluse critera in the dcount, i use that without even basing it on a query and go strait to the table, but that's another story

JHauge@jmjpc.net
Life is change. To deny change is to deny life.