"Kirk P." <(E-Mail Removed)> wrote in message
news:7D4541BD-9A2F-4AC4-A55E-(E-Mail Removed)
> It just displays it using Debug.Print. Here's my code:
>
> Public Function RecCount()
>
> Dim db As DAO.Database
> Dim rec As DAO.Recordset
>
> Set db = CurrentDb()
> Set rec = db.OpenRecordset("tblFinData")
>
> Debug.Print rec.RecordCount
>
> End Function
>
> So how do I actually return the number of records to the text box?
Make your function return a value. Change it like so:
'----- start of revised code -----
Public Function RecCount() As Long
Dim db As DAO.Database
Dim rec As DAO.Recordset
Set db = CurrentDb()
Set rec = db.OpenRecordset("tblFinData")
RecCount = rec.RecordCount
' Don't forget to clean up.
rec.Close
set rec = Nothing
Set db = Nothing
End Function
'----- end of revised code -----
Note the change to the function's declaration, as well as the changes
inside the function.
That said, there are two points worth mentioning. First is that the
RecordCount property of the recordset will only be accurate in this case
if the recordset is a table-type recordset. That will be true if
"tblFinData" is a local table (not a linked table nor a query), but not
otherwise. If you want to be sure the code will return an accurate
count in all circumstances, insert the line
If Not rec.EOF Then rec.MoveLast
immediately after the statement that opens the recordset.
The second point to mention is that you don't actually need to write
your own function to get a count of the records in a table or query.
The built-in DCount() function will do this. Instead of using your
function, your text box's ControlSource could be:
=DCount("*", "tblFinData")
Although the domain aggregate function such as DCount are not always the
fastest way to get the information, I'd probably use the built-in
function rather than write my own function just for this purpose.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
Similar Threads
