Number of records in a Recordset

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I wanted to use the following code in order to get the number of records in
a recordset.

Table called "Customers" have 25 records in there, but when I type in the
Immediate window the following:

GetRstRecordCount

I get the following:

1

How could this be. ?

I know that I can look at the table itself to get the number of records in
that table, but I first experienced this problem when I entered a criteria,
so I tried without any criteria, just the table, but the same happens.

Code start
**************************************************

Sub GetRstRecordCount()

Dim rstCustSub As DAO.Recordset
Set rstCustSub = CurrentDb.OpenRecordset("Customers")

Debug.Print rstCustSub.RecordCount

End Sub

*****************************************************
Code end


Thanks,

Scott
 
In order to get the recordcount from the code you're using, you would need
to put the following line in before your Debug.Print line:

rstCustSub.MoveLast

This actually populates the recordset you just created

If you want the count of records in a form's recordset, you can put the
following code in the Click Event of a command button:

MsgBox "My form has " & Me.RecordsetClone.RecordCount & " records."

This allows you to use the form's own recordset property rather than
creating and opening a new instance of a recordset.

To get the recordcount of another table - not serving as the recordset of
your form - you can use the DCount() function. For example, if you wanted
to know the number of orders sent to a certain region, you could use
DCount() as follows:

=DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")
 
Thanks,

did the job.

Scott


Cheryl Fischer said:
In order to get the recordcount from the code you're using, you would need
to put the following line in before your Debug.Print line:

rstCustSub.MoveLast

This actually populates the recordset you just created

If you want the count of records in a form's recordset, you can put the
following code in the Click Event of a command button:

MsgBox "My form has " & Me.RecordsetClone.RecordCount & " records."

This allows you to use the form's own recordset property rather than
creating and opening a new instance of a recordset.

To get the recordcount of another table - not serving as the recordset of
your form - you can use the DCount() function. For example, if you wanted
to know the number of orders sent to a certain region, you could use
DCount() as follows:

=DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Scott said:
I wanted to use the following code in order to get the number of records in
a recordset.

Table called "Customers" have 25 records in there, but when I type in the
Immediate window the following:

GetRstRecordCount

I get the following:

1

How could this be. ?

I know that I can look at the table itself to get the number of records in
that table, but I first experienced this problem when I entered a criteria,
so I tried without any criteria, just the table, but the same happens.

Code start
**************************************************

Sub GetRstRecordCount()

Dim rstCustSub As DAO.Recordset
Set rstCustSub = CurrentDb.OpenRecordset("Customers")

Debug.Print rstCustSub.RecordCount

End Sub

*****************************************************
Code end


Thanks,

Scott
Click to expand...
Click to expand...
 
Back
Top