Bug: Empty "If Me.Recordset Is Nothing Then" clause causes runtime error 3021 (Access 2003)

Meanwhile I searched for the ADO documentation of Requery. There is no
statement about BOF and EOF but according to
http://msdn.microsoft.com/library/de...adorequery.asp
calling Requery is "equivalent to calling the Close and Open methods in
succession". When you browse to the documentation of Open (at
http://msdn.microsoft.com/library/de...mthrstopen.asp)
you read:
"If the data source returns no records, the provider sets both the BOF and
EOF properties to True, and the current record position is undefined."

The bug remains. Microsoft should fix Requery in DAO and ADO.

Until this bug is fixed by Microsoft use this code after you call Requery on
a Recordset which could be empty and is bound to a form:

On Error Resume Next
If Me.Recordset.RecordCount = 0 Then Me.Recordset.MoveFirst

When Microsoft fixes Requery a call to MoveFirst will cause runtime error
3021. In order to prevent that Microsoft's Requery fix will break our fix
one day in the future add a "On Error Resume Next" line.

Boris

[...]

Have you tried the RecordSource = RecordSource work-around that I suggested,
Boris? It doesn't depend on the current behaviour, and so will not break if
that behaviour changes.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"Boris" wrote in message
...
Meanwhile I searched for the ADO documentation of Requery. There is no
statement about BOF and EOF but according to

http://msdn.microsoft.com/library/de...adorequery.asp
calling Requery is "equivalent to calling the Close and Open methods in
succession". When you browse to the documentation of Open (at

http://msdn.microsoft.com/library/de...mthrstopen.asp)
you read:
"If the data source returns no records, the provider sets both the BOF and
EOF properties to True, and the current record position is undefined."

The bug remains. Microsoft should fix Requery in DAO and ADO.

Until this bug is fixed by Microsoft use this code after you call Requery
on
a Recordset which could be empty and is bound to a form:

On Error Resume Next
If Me.Recordset.RecordCount = 0 Then Me.Recordset.MoveFirst

When Microsoft fixes Requery a call to MoveFirst will cause runtime error
3021. In order to prevent that Microsoft's Requery fix will break our fix
one day in the future add a "On Error Resume Next" line.

Boris

[...]

Brendan Reynolds wrote:
Have you tried the RecordSource = RecordSource work-around that I
suggested, Boris? It doesn't depend on the current behaviour, and so
will not break if that behaviour changes.

Ah, yes, that works, too! You are right, it is safer to use RecordSource =
RecordSouce.

Boris

[...]

cal.Requery is not the same as rest.Requery, (although you would
not know it by just leafing through the help files). The query
plan is cached, and unless it has changed (cal.recordsource =
cal.recordsource), requerying the object may not generate a dao/ado
requery.

calling Requery is "equivalent to calling the Close and Open
succession". When you browse to the documentation of Open (at

Ok, so lets get this straight :~) The help file (that well
known font of wisdom), says that requery is 'equivalant' to
open, therefore it follows that MS should change Access 2003
BOF/EOF behavior to match your expectations....

I agree that this could be an improvement on the system implemented
in Access 2, 95, 97, 2002, and XP, but I'm less than sanguine
about making changes to code that works 'good enough'.

On the other hand, I'm all in favour of making corrections to the
help files...

(david)


"Boris" wrote in message
...
Meanwhile I searched for the ADO documentation of Requery. There is no
statement about BOF and EOF but according to

http://msdn.microsoft.com/library/de...adorequery.asp
calling Requery is "equivalent to calling the Close and Open methods in
succession". When you browse to the documentation of Open (at

http://msdn.microsoft.com/library/de...mthrstopen.asp)
you read:
"If the data source returns no records, the provider sets both the BOF and
EOF properties to True, and the current record position is undefined."

The bug remains. Microsoft should fix Requery in DAO and ADO.

Until this bug is fixed by Microsoft use this code after you call Requery
on
a Recordset which could be empty and is bound to a form:

On Error Resume Next
If Me.Recordset.RecordCount = 0 Then Me.Recordset.MoveFirst

When Microsoft fixes Requery a call to MoveFirst will cause runtime error
3021. In order to prevent that Microsoft's Requery fix will break our fix
one day in the future add a "On Error Resume Next" line.

Boris

[...]

david epsom dot com dot au wrote:
cal.Requery is not the same as rest.Requery, (although you would
not know it by just leafing through the help files). The query
plan is cached, and unless it has changed (cal.recordsource =
cal.recordsource), requerying the object may not generate a dao/ado
requery.

calling Requery is "equivalent to calling the Close and Open
succession". When you browse to the documentation of Open (at

Ok, so lets get this straight :~) The help file (that well
known font of wisdom), says that requery is 'equivalant' to
open, therefore it follows that MS should change Access 2003
BOF/EOF behavior to match your expectations....

I agree that this could be an improvement on the system implemented
in Access 2, 95, 97, 2002, and XP, but I'm less than sanguine
about making changes to code that works 'good enough'.

In our test database a call to Requery updates RecordCount to 0 but not BOF
and EOF to false. In my opinion this is a bug in Access and not in the help
file. Or is there any reasonable explanation why BOF and EOF should be true
if RecordCount is set to 0?

Boris

[...]

"Boris" wrote in message
...
In our test database a call to Requery updates RecordCount to 0 but not
BOF
and EOF to false. In my opinion this is a bug in Access and not in the
help
file. Or is there any reasonable explanation why BOF and EOF should be
true
if RecordCount is set to 0?

Boris

PMFJI, but if there's no data in the recordset, then BOF and EOFshould be
true.

From the Help file:

The BOF property returns True if the current record position is before the
first record, and False if the current record position is on or after the
first record.

The EOF property returns True if the current record position is after the
last record, and False if the current record position is on or before the
last record.

When the recordset is empty, then there's no way that either can be False by
definition.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

Douglas J. Steele wrote:
"Boris" wrote in message
...
In our test database a call to Requery updates RecordCount to 0 but
not BOF and EOF to false. In my opinion this is a bug in Access and
not in the help file. Or is there any reasonable explanation why BOF
and EOF should be true if RecordCount is set to 0?

Boris

PMFJI, but if there's no data in the recordset, then BOF and
EOFshould be true.

You are right, Douglas, I explained it wrongly. The test database proves
that Access does *not* set BOF and EOF to true if the Recordset is empty -
it should do so according to the help files.

Boris

BOF and EOF are set by a MOVE command. If you do not do a MOVE,
then BOF and EOF will not be set. It is quite easy to get a
recordset with no records, but BOF and EOF both false: just delete
the records from the recordset so that it is empty. (This is,
or was, documented in the help file, and last I checked remains
true in all versions of Access).


There are a number of actions that implicitly do a MOVE action:
these implicitly set BOF and EOF. If the implicit implicits have
become to remote, or if you are doing deletes on the recordset,
you may wish to reconsider use of the MOVE flags.

(david)


"Douglas J. Steele" wrote in message
...
"Boris" wrote in message
...
In our test database a call to Requery updates RecordCount to 0 but not
BOF
and EOF to false. In my opinion this is a bug in Access and not in the
help
file. Or is there any reasonable explanation why BOF and EOF should be
true
if RecordCount is set to 0?

Boris

PMFJI, but if there's no data in the recordset, then BOF and EOFshould be
true.

From the Help file:

The BOF property returns True if the current record position is before the
first record, and False if the current record position is on or after the
first record.

The EOF property returns True if the current record position is after the
last record, and False if the current record position is on or before the
last record.

When the recordset is empty, then there's no way that either can be False
by
definition.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

david epsom dot com dot au wrote:
BOF and EOF are set by a MOVE command. If you do not do a MOVE,
then BOF and EOF will not be set. It is quite easy to get a
recordset with no records, but BOF and EOF both false: just delete
the records from the recordset so that it is empty. (This is,
or was, documented in the help file, and last I checked remains
true in all versions of Access).

That is what the test database does: Deleting all records from the
recordset. And my proposed fix was to call MoveFirst on the empty Recordset
to update BOF and EOF to true.

Searching the help files and MSDN I only found the stuff about Requery being
equivalent to Close and Open and therefore concluded that BOF and EOF should
be set to true. Do you remember where you read about BOF and EOF should be
false? I understand your point with Move actions. However in my opinion the
help files seem to contradict Access regarding Requery. It would be nice to
hear the opinion of someone from the Microsoft Access team.

Boris

[...]

DAO360 help: "If you delete the last remaining record in the Recordset
object, the BOF and EOF properties may remain False until you attempt to
reposition the current record."

I have some unresolved issues with requery/BOF/EOF, but in your case doesn't
it look more like a latency problem between your form recordset and your
project connection, rather than a failure of requery/BOF/EOF ???



(david)



"Boris" wrote in message
...
david epsom dot com dot au wrote:
BOF and EOF are set by a MOVE command. If you do not do a MOVE,
then BOF and EOF will not be set. It is quite easy to get a
recordset with no records, but BOF and EOF both false: just delete
the records from the recordset so that it is empty. (This is,
or was, documented in the help file, and last I checked remains
true in all versions of Access).

That is what the test database does: Deleting all records from the
recordset. And my proposed fix was to call MoveFirst on the empty
Recordset
to update BOF and EOF to true.

Searching the help files and MSDN I only found the stuff about Requery
being
equivalent to Close and Open and therefore concluded that BOF and EOF
should
be set to true. Do you remember where you read about BOF and EOF should be
false? I understand your point with Move actions. However in my opinion
the
help files seem to contradict Access regarding Requery. It would be nice
to
hear the opinion of someone from the Microsoft Access team.

Boris

[...]