Hi Frnds,

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

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

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

Bah, I always fall down on D-commands!