469,898 Members | 2,201 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

determine if recordset is empty BEFORE running movefirst

MLH
160 Dim DB As Database, Rst As Recordset, QD As QueryDef
170 Set DB = CurrentDb()
180 Set QD = DB.CreateQueryDef("", MySQL)
190 Set Rst = QD.OpenRecordset(dbOpenDynaset)
HOW TO COUNT RECORDS IN Rst BEFORE NEXT LINE?
200 Rst.MoveFirst

Mar 7 '08 #1
4 10418
MLH wrote:
160 Dim DB As Database, Rst As Recordset, QD As QueryDef
170 Set DB = CurrentDb()
180 Set QD = DB.CreateQueryDef("", MySQL)
190 Set Rst = QD.OpenRecordset(dbOpenDynaset)
HOW TO COUNT RECORDS IN Rst BEFORE NEXT LINE?
200 Rst.MoveFirst
Either...

If Not Rst.EOF Then...

Or

If Rst.RecordCount 0 Then...

Note that upon opening a Recordset the RecordCount cannot be trusted for an
accurate total count until you do a MoveLast, but it CAN be trusted to
indicate whether there are zero records or (something more than zero).

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Mar 7 '08 #2
DFS
Rick Brandt wrote:
Note that upon opening a Recordset the RecordCount cannot be trusted
for an accurate total count until you do a MoveLast, but it CAN be
trusted to indicate whether there are zero records or (something more
than zero).
Is that true for DISTINCT queries as well? I seem to remember doing some
tests and it always returned the correct recordcount w/o MoveLast.

Mar 8 '08 #3
DFS wrote:
Rick Brandt wrote:
>Note that upon opening a Recordset the RecordCount cannot be trusted
for an accurate total count until you do a MoveLast, but it CAN be
trusted to indicate whether there are zero records or (something more
than zero).

Is that true for DISTINCT queries as well? I seem to remember doing
some tests and it always returned the correct recordcount w/o
MoveLast.
I didn't mean to imply that it would always be incorrect without a MoveLast,
just that it often is. For it to be "trusted" it would beed to be correct
100% of the time and it clearly does not meet that standard.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Mar 8 '08 #4
"DFS" <nospam@dfs_.comwrote in
news:p_****************@bignews2.bellsouth.net:
Rick Brandt wrote:
>Note that upon opening a Recordset the RecordCount cannot be trusted
for an accurate total count until you do a MoveLast, but it CAN be
trusted to indicate whether there are zero records or (something more
than zero).alted perhaps

Is that true for DISTINCT queries as well? I seem to remember doing
some tests and it always returned the correct recordcount w/o
MoveLast.
I think that's correct. I am guessing that JET uses or creates a unique
index to do a Select Distinct and that the recordset inherits the index's
DistinctCount property. (Debug.Print DBEngine(0)(0).TableDefs
("Employees").Indexes("LastName").DistinctCount )

Table type recordsets return the correct recordcount on opening.

ADO Recordsets are a different kettle of fish, of course.

--
lyle fairfield
Mar 8 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by David Berry | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.