Code to count records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Shel - 03 Mar 2005 20:29 GMT

Does anybody out there have any VBA code that will allow me to count the
number of records returned by a query?

Signature

Thanks in advance!
Shel

Reply to this Message

RD - 03 Mar 2005 20:56 GMT

>Does anybody out there have any VBA code that will allow me to count the
>number of records returned by a query?

DAO:

Dim db As DAO.Database, rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("Query Name")
Debug.Print rs.RecordCount

There is a very involved example in the Help file. I'll post that if
you like.

HTH,
RD

Reply to this Message

Shel - 03 Mar 2005 21:17 GMT

in the VBA help file? Yes, please post it. I would be very thankful.

Thanks
Shel

> >Does anybody out there have any VBA code that will allow me to count the
> >number of records returned by a query?
[quoted text clipped - 12 lines]
> HTH,
> RD

Reply to this Message

RD - 03 Mar 2005 22:03 GMT

>in the VBA help file? Yes, please post it. I would be very thankful.
>
[quoted text clipped - 17 lines]
>> HTH,
>> RD

Here 'tis:

RecordCount Property Example

This example demonstrates the RecordCount property with different
types of Recordsets before and after they're populated.

Sub RecordCountX()

Dim dbsNorthwind As Database
Dim rstEmployees As Recordset

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

With dbsNorthwind
' Open table-type Recordset and show RecordCount
' property.
Set rstEmployees = .OpenRecordset("Employees")
Debug.Print _
"Table-type recordset from Employees table"
Debug.Print " RecordCount = " & _
rstEmployees.RecordCount
rstEmployees.Close

' Open dynaset-type Recordset and show RecordCount
' property before populating the Recordset.
Set rstEmployees = .OpenRecordset("Employees", _
dbOpenDynaset)
Debug.Print "Dynaset-type recordset " & _
"from Employees table before MoveLast"
Debug.Print " RecordCount = " & _
rstEmployees.RecordCount

' Show the RecordCount property after populating the
' Recordset.
rstEmployees.MoveLast
Debug.Print "Dynaset-type recordset " & _
"from Employees table after MoveLast"
Debug.Print " RecordCount = " & _
rstEmployees.RecordCount
rstEmployees.Close

' Open snapshot-type Recordset and show RecordCount
' property before populating the Recordset.
Set rstEmployees = .OpenRecordset("Employees", _
dbOpenSnapshot)
Debug.Print "Snapshot-type recordset " & _
"from Employees table before MoveLast"
Debug.Print " RecordCount = " & _
rstEmployees.RecordCount

' Show the RecordCount property after populating the
' Recordset.
rstEmployees.MoveLast
Debug.Print "Snapshot-type recordset " & _
"from Employees table after MoveLast"
Debug.Print " RecordCount = " & _
rstEmployees.RecordCount
rstEmployees.Close

' Open forward-only-type Recordset and show
' RecordCount property before populating the
' Recordset.
Set rstEmployees = .OpenRecordset("Employees", _
dbOpenForwardOnly)
Debug.Print "Forward-only-type recordset " & _
"from Employees table before MoveLast"
Debug.Print " RecordCount = " & _
rstEmployees.RecordCount

' Show the RecordCount property after calling the
' MoveNext method.
rstEmployees.MoveNext
Debug.Print "Forward-only-type recordset " & _
"from Employees table after MoveNext"
Debug.Print " RecordCount = " & _
rstEmployees.RecordCount
rstEmployees.Close

.Close
End With

End Sub

Reply to this Message

John Vinson - 03 Mar 2005 21:40 GMT

>Does anybody out there have any VBA code that will allow me to count the
>number of records returned by a query?

nRecords = DCount("*", "queryname")

John W. Vinson[MVP]

Reply to this Message

Shel - 04 Mar 2005 18:23 GMT

This worked exactly as I wanted! THANK YOU VERY MUCH!

> >Does anybody out there have any VBA code that will allow me to count the
> >number of records returned by a query?
>
> nRecords = DCount("*", "queryname")
>
> John W. Vinson[MVP]

Reply to this Message

David C. Holley - 04 Mar 2005 05:17 GMT

Its a bit late, so I may not have this entirely correct but something to
the effect of...

Set qdf = CurrentDatabase.QueryDefs(queryName)
Set rs = qdf.openrecordset (dbForwardOnly)
if not rs.EOF then
rs.movelast
Debug.print rs.recordCount
Else
Debug.print "No records retured"
end if
set rs = nothing
set qdf = nothing

MVP's how did I do off the top of my head?
(Assuming there are no query parameters)

David H

> Does anybody out there have any VBA code that will allow me to count the
> number of records returned by a query?

Reply to this Message