OpenRecordSet returns incorrect number of records

[Joe Cletcher]

If I use the following query in the database window, the query returns the
correct number of records (22). However, if I open the recordset from within
a VBA subroutine the query returns only 1 record. If the query were to work
correctly, the hard coded value of "81" would be replaced with a variable
similar to "aTrngAreaID".

Dim aTrngAreaID As Long
Dim db As DAO.Database
Dim QryTxt As String
Dim rs As DAO.Recordset
Dim rsAreas As DAO.Recordset
Dim rsAreaTrng As DAO.Recordset
Dim rsPersons As DAO.Recordset

Set db = CurrentDb
..
..
..

QryTxt = "SELECT TrngTypeArea.TrngTypeID FROM TrngTypeArea WHERE
(((TrngTypeArea.TrngAreaID)=81))"
Set rsAreaTrng = db.OpenRecordset(QryTxt)

 

[Stefan Hoffmann]

hi Joe,

If I use the following query in the database window, the query returns the
correct number of records (22). However, if I open the recordset from within
a VBA subroutine the query returns only 1 record. If the query were to work
correctly, the hard coded value of "81" would be replaced with a variable
similar to "aTrngAreaID".

The value .RecordCount depends on various parameter. Use a .MoveLast to
get the correct numbers.


mfG
--> stefan <--

 

[Tim Ferguson]

QryTxt = "SELECT TrngTypeArea.TrngTypeID FROM TrngTypeArea WHERE
(((TrngTypeArea.TrngAreaID)=81))"

QryTxt = "SELECT COUNT(TryTypeID) AS NumTypes" & _
"FROM TrngTypeArea " & +
"WHERE TrngAreaID = 81;"

NumberOfTypes = db.OpenRecordset( _
QryTxt, dbOpenSnapshot, dbForwardOnly _
).Fields("NumTypes")



(could also be something like

NumberOfTypes = DCount("TrngTypeID", _
"TrngTypeArea", _
"WHERE TrngAreaID = 81;")

)


Hope that helps


Tim F

 

[Joe Cletcher]

Thanks for jogging my memory--I now remember same problem from years ago.