Display # of Records in a text box

[Kirk P.]

I've got this function called RecCount that correctly gives me the number of
records in a table called tblFinData.

Now, I want to display the number of records calculated by this function in
a text box. What's the secret to actually getting this number to appear in
my text box? I can make it appear in the Immediate window, but that's it.

 

[Dirk Goldgar]

I've got this function called RecCount that correctly gives me the
number of records in a table called tblFinData.

Now, I want to display the number of records calculated by this
function in a text box. What's the secret to actually getting this
number to appear in my text box? I can make it appear in the
Immediate window, but that's it.

Does the function RecCount actually return the number of records, or
just display it using Debug.Print? If the former, you should be able to
set the ControlSource of your text box to:

=RecCount()

 

[Kirk P.]

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?

 

[Dirk Goldgar]

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.

 

[Kirk P.]

Perfect - thanks for the help!

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)

 

[David C. Holley]

Why not set the control source for the specific text box to

=DCount(fieldName, tableName, criteria)

 

[David C. Holley]

If you go with this, be certain to SET the object variables to NOTHING.

Set rec = NOTHING
Set db = NOTHING

David H

 

[MikeB]

If the recordset is the record source for the form, then try the following VB
code:

me.txtControlName = me.recordset.recordcount

or,

me.txtControlName.value = me.recordset.recordcount

where txtControlName is the name of the text box you want the count
displayed. This short code is all you need when run inside a form. Run it
when first displaying the form or adding/deleting records. I call it from
the Form_Current() procedure.