Opening a recordset in a function

[Bill H]

I am trying to open a recordset in a function, using DAO syntax. If I
specify a table name, the record count for the recordset is correct.
However, if I use a SQL statemet, the record count is only one... Any help is
well appreciated....


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

MsgBox "rsopen"
rs.Close
Set rs = Nothing

End Function

 

[louisjohnphillips]

I am trying to open a recordset in a function, using DAO syntax. If I
specify a table name, the record count for the recordset is correct.
However, if I use a SQL statemet, the record count is only one... Any help is
well appreciated....

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

MsgBox "rsopen"
rs.Close
Set rs = Nothing

End Function

I have always seen

rs.MoveFirst
rs.MoveLast

before.

MsgBox rs.RecordCount

However, I'm more experienced using ADO.

 

[UpRider]

Bill, here's a function that will give you a recordcount of a query or
table:
Just pass the name of the query or table.

function fcnCountRecs(strName as string) as long
fcncountRecs = dcount("*",strName)
end function

HTH, UpRider

 

[Steve Schapel]

Bill,

Except in the case of a recordset opened directly on a local table, as
you have mentioned, the recordset is only populated as accessed.
Therefore, to get an accurate record count, you need to traverse the
recordset first. Try it like this:

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