Want to get table record count

[Tom]

I want to get the number of records in the table. Seems to me this worked
before, in another module. Does anyone know what I'm doing wrong?

Set DAOdbs = CurrentDb
'------------------------------------------------------------
'
' Open the 'Detail' table
'
Set DAOrs = DAOdbs.OpenRecordset(strDetailTableName)

intNmbrOfRecs = DAOdbs.TableDefs(strDetailTableName).RecordCount

DAOrs.Close
Set DAOrs = Nothing

DAOdbs.Close
Set DAOdbs = Nothing

Thanks in advance,

Tom

 

[Paul]

This will work:

dim howmanyrecords As Integer
howmanyrecords = DCount("*", "tablename")

msgbox howmanyrecords

Paul

 

[David C. Holley]

1. For the technique that you're using, you have to move to the last
record using .MoveLast method. In your code you would use
intNmbrOfRecs.MoveLast right before the .RecordCount statement.

2. You might find that using DCount() is easier and/or faster.
DCount(anyFieldName, tableName)

 

[Dirk Goldgar]

1. For the technique that you're using, you have to move to the last
record using .MoveLast method. In your code you would use
intNmbrOfRecs.MoveLast right before the .RecordCount statement.

He wouldn't have to *if* the table is a local (unlinked) Jet table, so
that the recordset is a table-type recordset. In that specific case,
the RecordCount property is accurate when the recordset is first opened.
For that matter, in that case the record count can be gotten from the
TableDef without even opening a recordset on it. So if this line

intNmbrOfRecs = DAOdbs.TableDefs(strDetailTableName).RecordCount

isn't working, the table is probably a linked table. If that's a case,
Tom does need to open a recordset, move to the end of it, and then get
the RecordCount property of the *recordset*, not the tabledef.