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
set rst = nothing
strMsg = "There are " & lngCount & " record(s) in " & strTableName
msgbox strMsg, vbInformation, strTitle
pfunCountRecords = lngCount
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.