record COUNT() query not working in VBA code

[BrianB via AccessMonster.com]

I am using the following query in my code to count the number of records
associated with a selected Client. I originally generated this SQL statement
in the query wizard and it worked fine and returns the right number of
records (of course asking me the for the client number [fselectrecd] which
normally comes from a combobox). I copied the code directly from the query
into my VBA code.
This code always returns a value of 1, even if there are 0 records.
When I "hover" over the [Forms]![fcontribution]![fSelectRecd] when I break
for debugging it shows the correct client number to count. I even tried VAL()
& VAL(TRIM()) on the combobox field in case a field type mismatch existed. I
also tried without the extra brackets the query builder put in.
Suggestions please

Dim strSQL As String
Dim rstAny As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
strSQL = "SELECT Count(*) as Expr1 FROM Contribution " _
& "WHERE (((Contribution.ClientID)=" & [Forms]![fcontribution]!
[fSelectRecd] & "));"
Set rstAny = db.OpenRecordset(strSQL)
RecdCount = rstAny.RecordCount

 

[Allen Browne]

Perhaps the recordset is returning 1 record, and that record gives the count
as zero?

 

[Tom van Stiphout]

On Sat, 17 Oct 2009 03:41:26 GMT, "BrianB via AccessMonster.com"

I'm going to have a rare disagreement with Allen here. I think you
cannot inspect the RecordCount property and expect it to be accurate
without first moving to the last record:
Set rstAny = db.OpenRecordset(strSQL)
if rstAny.EOF = False then rstAny.MoveLast
RecdCount = rstAny.RecordCount
This is because of lazy loading of a recordset object.

Looking at your query in more detail: why would you want to run a
"SELECT COUNT(*)" query and then check the RecordCount property? OF
COURSE it would return one record. It seems likely that in an earlier
iteration of your program you selected all appropriate records into
the set (without any domain aggregate queries) and then you had to
move to the last record to check the Count; now since you changed the
query to "select count" you should have also modified the RecordCount
line but you forgot to do so.

In one word, your code is inconsistent, but you can fix it.

-Tom.
Microsoft Access MVP

 

[BBC via AccessMonster.com]

Thanks Allen & Tom for you feedback.

I woke up in the middle of the night last night and had an "aha" moment and
realized what was wrong. As you implied Tom, of course it will only return 1
record with the count being in the expr1 field (I should have picked up on
that as that is what was returned by the query when run in the wizard). I
simply replaced the last line of code with
RecdCount = [rstAny]![expr1]
and it now works.

Thanks guys
Appreciated as always

I'm going to have a rare disagreement with Allen here. I think you
cannot inspect the RecordCount property and expect it to be accurate
without first moving to the last record:
Set rstAny = db.OpenRecordset(strSQL)
if rstAny.EOF = False then rstAny.MoveLast
RecdCount = rstAny.RecordCount
This is because of lazy loading of a recordset object.

Looking at your query in more detail: why would you want to run a
"SELECT COUNT(*)" query and then check the RecordCount property? OF
COURSE it would return one record. It seems likely that in an earlier
iteration of your program you selected all appropriate records into
the set (without any domain aggregate queries) and then you had to
move to the last record to check the Count; now since you changed the
query to "select count" you should have also modified the RecordCount
line but you forgot to do so.

In one word, your code is inconsistent, but you can fix it.

-Tom.
Microsoft Access MVP

I am using the following query in my code to count the number of records
associated with a selected Client. I originally generated this SQL statement

[quoted text clipped - 18 lines]

Set rstAny = db.OpenRecordset(strSQL)
RecdCount = rstAny.RecordCount