RECORDSET COUNT ALWAYS 1

[LBERNARDE]

I'm trying to count the number or records in a recordset. It always returns 1. I'm extremely stuck.

I want to pull all records from a table that the class is master. Then I need to count this number and then will go from there assigning awards. I need to know the count to determine how many positions to award. Any ideas? Please any advice would be appreciated. I'v completely hit a road block
Sub SQLX()
Dim dbsOshkosh As Database
Dim qdfTemp As QueryDef
Dim rstClass As Recordset
Dim strsqloutput As String


Set dbsOshkosh = CurrentDb
Set qdfTemp = dbsOshkosh.CreateQueryDef("&quot

SQLOutput "SELECT * FROM tblsmalborepositionawards " & _
"WHERE tblsmalborepositionawards!class = 'MASTER' " & _
"ORDER BY CLASS", qdfTemp

dbsoshkosh.Close

End Sub

Function SQLOutput(strSQL As String, qdfTemp As QueryDef)

Dim rstClass2 As Recordset
Dim strreccount As String
' Set SQL property of temporary QueryDef object and open
' a Recordset.
qdfTemp.SQL = strSQL
Set rstClass2 = qdfTemp.OpenRecordset
strreccount = rstClass2.AbsolutePosition
'rstEmployees.recordcount
strreccount = rstClass2.recordcount

Debug.Print strSQL

With rstclass2
' Enumerate Recordset.
Do While Not .EOF
Debug.Print " " & !CLASS
.MoveNext
Loop
.Close
End With

End Function

 

[cascot]

Hi LBERNARDE,

In order to get an accurate count of records in a recordset, you must first move the pointer to the last record.

Try something like this:

With rs
.MoveLast
rsRecCount = .RecordCount
.MoveFirst
[whatever else you need to do with the recordset]
.Close
End With
Set rs = Nothing

 

[cmmrfrds]

I am not sure what version you are using but in the days prior to Access 2000 and ADO, there was a bug where your had to do a movelast and movefirst before accessing the record count.