Can't get TOP 10

[Joao]

Private Sub procLerUltDezNumInseridos()
SQLString = "SELECT TOP 10 NrSubscritor FROM Processos WHERE UserID = " _
& lngUserNumber & " ORDER BY TimeStamp DESC;"

funcCorrerSQL SQLString
End Sub

Public Function funcCorrerSQL(strCmdSQL As String)
Set rstSQL = BD.OpenRecordset( _
strCmdSQL, dbOpenDynaset)

VarTemp = 0
Registos = rstSQL.RecordCount
While Registos > 0
ObjLista.AddItem rstSQL.Fields(0), VarTemp
rstSQL.MoveNext
VarTemp = VarTemp + 1
Registos = Registos - 1
Wend

rstSQL.Close
BD.Close
end function

If I remove TOP 10 from the SQL I get all the data, if not I just get one
record.
If I run this query in Access via SQL window it works... i really just don't
understand...

 

[Alex Dybenko]

Hi,
you have to go to the end of the recordset before checking for RecordCount

if not rstSQL.eof then
rstSQL.MoveLast
rstSQL.MoveFirst
Registos = rstSQL.RecordCount
end if

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Stefan Hoffmann]

hi Joao,

Use a loop like

Dim rs As DAO.Recordset

Set rs = CurrentDbC.OpenRecordset("", dbOpenSnapshot)
If Not rs.BOF And Not rs.EOF Then
Do While Not rs.EOF

ObjLista.AddItem rs.Fields(0), VarTemp
VarTemp = VarTemp + 1

rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing

mfG
--> stefan <--

 

[Joao]

Thank you Alex, that blown me away