Record Count in a Continuous Form

[molsonexpert]

OK, this should be easy, but here goes:

I have an Access 2002 application with a link to a SQL 2000 table. I've
created a query based on the table, and created a continuous form based on
the query. What is the best way to count the number of records returned? On
the form, I've added a text box with =count(NameID) as the control source in
the form footer, and this actually works, unless no records exist, in which
case the text box is blank (I'm hoping it will be 0). I ultimately want to
display a message box, the content of which will depend on the value in this
text box.

tia.

 

[Roger Carlson]

If the query is a saved query, you could use DCount.

msgbox "There are " & DCount("[FieldName]", "[QueryName]") & " records in
the form."

Replace Fieldname and QueryName with your actual query and a field which
will have a value in every field.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Roger Carlson]

If it's NOT a saved query, you could open a recordset based on the query and
use the recordcount property to return the number of records. This one uses
DAO.

dim db as dao.database
dim rs as dao.recordset
set db = currentdb
set rs = db.openrecordset("Select * from Table where yada=yada
if not rs.EOF then
rs.movelast
Msgbox "There are " & rs.recordcount & " records in the form."
else
Msgbox "There are 0 records in the form."
end if
set rs = nothing
set db = nothing

--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[molsonexpert]

Perfect.

--
steve.

If the query is a saved query, you could use DCount.

msgbox "There are " & DCount("[FieldName]", "[QueryName]") & " records in
the form."

Replace Fieldname and QueryName with your actual query and a field which
will have a value in every field.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

OK, this should be easy, but here goes:

I have an Access 2002 application with a link to a SQL 2000 table. I've
created a query based on the table, and created a continuous form based
on the query. What is the best way to count the number of records
returned? On the form, I've added a text box with =count(NameID) as the
control source in the form footer, and this actually works, unless no
records exist, in which case the text box is blank (I'm hoping it will be
0). I ultimately want to display a message box, the content of which will
depend on the value in this text box.

tia.