Open a dao recordset

[William Hardin]

I am writing a function in which I need to open a sorted recordset. I am
using a SQL statemenet in the db.OpenRecordset line, but it is only
returning one record, when executing the query outside the function should
return 153. Here is the basic code...
Function FirstOpen() As Integer

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strQry As String

strQry = "SELECT TblCDS.* FROM TblCDS;"

Set db = CurrentDb
Set rs = db.OpenRecordset(strQry, dbOpenDynaset)
MsgBox rs.RecordCount

rs.Close
Set rs = Nothing

End Function

If I use the table name instead of the SQL, the message box returns the
correct count. Why doesn't this work?

[John Winterbottom]

try adding these lines:

Set rs = db.OpenRecordset(strQry, dbOpenDynaset)
rs.MoveLast
rs.MoveFirst

to fully populate the recordset

William Hardin wrote:

I am writing a function in which I need to open a sorted recordset. I am
using a SQL statemenet in the db.OpenRecordset line, but it is only
returning one record, when executing the query outside the function should
return 153. Here is the basic code...
Function FirstOpen() As Integer

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strQry As String

strQry = "SELECT TblCDS.* FROM TblCDS;"

Set db = CurrentDb
Set rs = db.OpenRecordset(strQry, dbOpenDynaset)
MsgBox rs.RecordCount

rs.Close
Set rs = Nothing

End Function

If I use the table name instead of the SQL, the message box returns the
correct count. Why doesn't this work?