Code to count records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

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
 
Does anybody out there have any VBA code that will allow me to count the
number of records returned by a query?
Click to expand...

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

John W. Vinson[MVP]
 
in the VBA help file? Yes, please post it. I would be very thankful.

Thanks
Shel
Click to expand...

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
 
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
 
Back
Top