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

--
Thanks in advance!
Shel

On Thu, 3 Mar 2005 12:29:05 -0800, "Shel"
<(E-Mail Removed)> wrote:

>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

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

Thanks
Shel

"RD" wrote:

> On Thu, 3 Mar 2005 12:29:05 -0800, "Shel"
> <(E-Mail Removed)> wrote:
>
> >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
>
>

On Thu, 3 Mar 2005 12:29:05 -0800, "Shel"
<(E-Mail Removed)> wrote:

>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]

On Thu, 3 Mar 2005 13:17:04 -0800, "Shel"
<(E-Mail Removed)> wrote:

>in the VBA help file? Yes, please post it. I would be very thankful.
>
>Thanks
>Shel
>
>"RD" wrote:
>
>> On Thu, 3 Mar 2005 12:29:05 -0800, "Shel"
>> <(E-Mail Removed)> wrote:
>>
>> >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
>>
>>

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

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

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

"John Vinson" wrote:

> On Thu, 3 Mar 2005 12:29:05 -0800, "Shel"
> <(E-Mail Removed)> wrote:
>
> >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]
>