rs.RecordCount linked to a sql server db

[smook]

I have some code in an ONCURRENT event that is running a query and then counting the number of records returned. However the RecordCount is always returning 1. I know the query works .. i put a msgbox and capture the query its running and when i run it in a query it returns the all the records.

The interface is in access but it is linked to a SQLServer database...

Dim db As Database
Dim rs, rs2 As Recordset
Dim strSql As String
Dim orgCount, nomCount, estimate As Integer

'Initialize to return Null on error.
Set db = CurrentDb

strSql = "SELECT qryCeremonyProgram.organizationName FROM qryCeremonyProgram where qryCeremonyProgram.ceremonyId = " & Parent.ceremonyid & " GROUP BY qryCeremonyProgram.organizationName"

Set rs = db.OpenRecordset(strSql)

orgCount = rs.RecordCount 'Return the number of distinct records.
MsgBox (orgCount)

[smook]

Oh ... well I solved it myself. I guess you have to move through the recordset first so adding:

rs.moveLast

before the rs.RecordCount seemed to clear that up.

[ADezii]

Oh ... well I solved it myself. I guess you have to move through the recordset first so adding:

rs.moveLast

before the rs.RecordCount seemed to clear that up.

You hit the nail on the head, some Recordsets you must explicitly traverse in order to get an accurate RecordCount, some Recordsets return the number of Records actually accessed (1), which seems to be your case. Whenever I need an accurate RecordCount, I always include the following line in my code:

rs.MoveLast:rs.MoveFirst