p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   Count Record (http://p2p.wrox.com/showthread.php?t=50713)

prasanta2expert November 24th, 2006 07:37 AM

Count Record
 
Hi Frnds,

          How to count the record in a database and show the result in a messagebox? I want to do it using VBA.


leehambly November 27th, 2006 07:28 AM

Prasanta,

Very simply, to count the records in a table and return the value in a msgbox...

================================================== ===============
Public Function pfunCountRecords(strTableName as string) as Long
dim rst as recordset
dim strMsg as string
dim strTitle as string
dim lngCount as long

strTitle = "Record Count"
set rst = currentdb.openrecordset(strTableName)
lngCount = rst.recordcount

rst.close
set rst = nothing

strMsg = "There are " & lngCount & " record(s) in " & strTableName
msgbox strMsg, vbInformation, strTitle

pfunCountRecords = lngCount

End Function
================================================== ===============

So, as a function this returns the record count value as part of the function, but also pops up a msgbox with the same information.

If it is more than a table you want to evaluate, change the input to a SQL statement instead and evaluate that.

Lee

SerranoG November 28th, 2006 10:24 AM

The easiest way to count the number of records in a table in a database is simply to use the DCOUNT function.

MsgBox "The number of records in the table is " & DCount("[lngRecNo]", "tblYourTable"), vbInformation, "Record Count"

Where you replace lngRecNo and tblYourTable with the real names of a non-null field in your table and the actual name of your table. I used these fake names instead.

For a small database, DCOUNT is easier than Lee's answer. For a huge database, Lee's more complex answer will work faster than DCOUNT.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

leehambly November 28th, 2006 10:42 AM

Bah, I always fall down on D-commands!


All times are GMT -4. The time now is 02:46 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.