Number of records from Query

[Guest]

Hello,

Lets say I have a Query already created & the Query Name is "qryCustomersInNJ"
where the SQL code is
SELECT COUNT(*) AS NumOfRecords FROM tblCustomers WHERE State = 'NJ'


Now in my VBA code I want to get what the Count is using a Recordset.
Note I dont want to use the DCOUNT function.

How can I do this ?

I know I can use:

strRecordSetSource = "SELECT COUNT(*) AS NumOfRecords FROM tblCustomers
WHERE State = 'NJ'"
Set rst = CurrentDb.OpenRecordset(strRecordSetSource, dbOpenForwardOnly)
dblNumOfRecords = rst!NumOfRecords
rst.Close

Where this uses the SQL assigned to a string variable.

What I am looking for help on is if the Query is already created & then I
want to get the count on it without having to put the SQL in my VBA Code.

I tried:

Set rst = CurrentDb.OpenRecordset("qryCustomersInNJ", dbOpenForwardOnly)
dblNumOfRecords = rst!NumOfRecords
rst.Close

But I get an err 3061 "Too few parameters. Expected 2"

Can someone help me again get # of Records for an already Created Query ?

Any help would be greatly appreciated.

Thank you,
Jeff

 

[Marshall Barton]

Lets say I have a Query already created & the Query Name is "qryCustomersInNJ"
where the SQL code is
SELECT COUNT(*) AS NumOfRecords FROM tblCustomers WHERE State = 'NJ'


Now in my VBA code I want to get what the Count is using a Recordset.
Note I dont want to use the DCOUNT function.

I know I can use:

strRecordSetSource = "SELECT COUNT(*) AS NumOfRecords FROM tblCustomers
WHERE State = 'NJ'"
Set rst = CurrentDb.OpenRecordset(strRecordSetSource, dbOpenForwardOnly)
dblNumOfRecords = rst!NumOfRecords
rst.Close

Where this uses the SQL assigned to a string variable.

What I am looking for help on is if the Query is already created & then I
want to get the count on it without having to put the SQL in my VBA Code.

I tried:

Set rst = CurrentDb.OpenRecordset("qryCustomersInNJ", dbOpenForwardOnly)
dblNumOfRecords = rst!NumOfRecords
rst.Close

But I get an err 3061 "Too few parameters. Expected 2"

Can someone help me again get # of Records for an already Created Query ?

Dcount is the easiest way and just as fast as using a
recordset.

The error you received indicates that you misspelled the
name of a field or forgot to put quotes around a string.

 

[Guest]

Marshall,

Thank u for the response, but u are incorect about the speed of Dcount.
It is much slower when u have a Access Database set up as a Client / Server
application. See www.granite.ab.ca/access/performancefaq.htm
Should never use Dcount, Dlookup, Dsum, etc. that is why I specifically put
I dont want to use DCOUNT.

Any else out there can give me some help ?

Thank you,
Jeff

 

[Marshall Barton]

Did you find the misspelled name and/or missing quotes? If
your query's SQL view really is what you posted earlier, the
only way I can see of getting that message is if the field
in the table is named something other than State and you
forgot the quotes around NJ. AFAICS, the VBA is fine.

 

[JethroUK©]

have you tried

..Recordcount property

 

[Douglas J. Steele]

How did you declare rst?

Presumably you used

Dim rst As Recordset

If so, try

Dim rst As DAO.Recordset

instead.

I'm guessing that you've got references set to both ADO and DAO in your
application. When you have both references, you'll find that you'll need to
"disambiguate" certain declarations, because objects with the same names
exist in the 2 models. For example, to ensure that you get a DAO recordset,
you'll need to use Dim rsCurr as DAO.Recordset (to guarantee an ADO
recordset, you'd use Dim rsCurr As ADODB.Recordset). If you don't
disambiguate, Access will take the first reference it finds with a Recordset
object in it, and the ADO reference is almost always above the DAO reference
in the list.

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset