OpenRecordset anomaly

[Mark Hammer]

Problem: opening a recordset in code returns only one record when it should
return two.

Details:

In VBA I construct a SQL string as follows:

strSQL = _
"SELECT FacultyID_NUM, LOAD_PERCENTAGE FROM tblFacultyLoadTable_Local
WHERE CRS_CDE='WR 323 EB'
AND tblFacultyLoadTable_Local.Year=2007
AND tblFacultyLoadTable_Local.Term=1;"

Debug.Print strSQL

Then I open a DAO.recordset as follows:
Set rsTemp = _
CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

and check the records returned:
debug.print rsTemp.RecordCount

I get one record.

Trouble is, if I copy the SQL from the Immediate window and paste it into
the SQL pane of a new query, it returns two records, which is what I am
expecting.

How can it be that the recordset opened in code returns one record, while a
query having the identical SQL returns two records?

Mark Hammer
Puzzled in Lake Oswego, Oregon

 

[George Nicholson]

Check the DAO help file for the behavior of Recordcount. For Snapshots,
Recordcount won't return the correct value until all records have been
accessed with MoveLast. If you do that, you'll probably get the expected
value.

In other words: the recordset is fine, you just have an additional hoop to
jump through before Recordcount is reliable.

HTH,

 

[Mark Hammer]

Bingo! Thanks!

--Mark Hammer

Check the DAO help file for the behavior of Recordcount. For Snapshots,
Recordcount won't return the correct value until all records have been
accessed with MoveLast. If you do that, you'll probably get the expected
value.

In other words: the recordset is fine, you just have an additional hoop to
jump through before Recordcount is reliable.

HTH,