rstmeet.recordcount only returning one value

[Duritz]

Hi all, I have the following code:

Sub getoverall()

Sheets("overall record").Select
Calculate

Range("ad2:ad3").ClearContents




Dim sql_st As String

aruns = ActiveSheet.Range("z1").Value
awins = ActiveSheet.Range("aa1").Value


Set wrkjet = CreateWorkspace("", "admin", "", dbUseJet)

Set dbsfile = wrkjet.OpenDatabase("c:\research IV.mdb")

sql_st = "Select * FROM data Where L20S = " & aruns & " AND L20W = " & awins & ""

Set rstmeet = dbsfile.OpenRecordset(sql_st, dbOpenDynaset)
If rstmeet.RecordCount > 0 Then



With rstmeet
Range("ad3").Value = rstmeet.RecordCount


End With


End If

End sub

In both cells z1 and aa1 is 0. When I do a query in the Access database to which this code refers, there are ~78,000 records where L20S = 0 and L20W = 0, yet the rstmeet.recordcount always comes up as 1 record only. Not sure if it's of any relevance but both the L20STARTS field nor the L20WINS field are NOT indexed, does that make any difference?

Any ideas why this might be? Have examined the code back to front but can't work out why.

 

[Fazza]

Hi Duritz,

AFAIK, RecordCount is not supported. It just isn't available.

You'll need to loop through the recordset: something like, while not EOF, do whatever, movenext, loop.

If you only wanted the count, and I'm pretty sure that is not what you want, change the query to "SELECT count(*) FROM data WHERE ... "

HTH. Regards, Fazza

 

[Richard Schollar]

Isn't it a case you need to move to the last record and then access RecordCount (may be wrong here). So give:

With rstmeet
  .MoveLast
  MsgBox .RecordCount
End With

and see if it reports anything else.

 

[Fazza]

Thanks, Richard, I don't know. It isn't something I use & I can't test it here. I'll have a look tomorrow. Regards, Fazza