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

[¤Ñ²P]

¤j®a¦n!

«Ü«_¬Nªº¥´ÂZ§A­Ì,§Ú·Q³q¹L§A­Ì·s»D²Õ,´M§ä96¦~»{ÃѪº¤@¦ìªB¤Í,·í®É¦o¦bªZº~ªÅ¤l
¤¤¾ÇŪ®Ñ,¨º¦~¦o17©P·³.¦W¦r:·¨¨qÄR&?·¨·¨ÄR Ū°ª3,¦o¬Oªe«n¶}«Ê¤H,¦oª¨ª¨¬O­¸¦æ
­û,
TEL:0378-2930775-21239,²{¦b¹q¸Ü¤]¥´¤£³q,§Ú­Ì¬O¦bºû¹Å·È¦B³õ»{ÃѪº,
98¦~6¤ë,§Ú´N¨ìªF²ð¤F,¦Ü¤µ¨S¦³Ápµ¸¨ì,§Æ±æ»{ÃѩΪ̪¾¹D¦oªºªB¤Í½Ð§i¶D¦o,
¤]ªÌ©Î½Ð©M§ÚÁpµ¸.
¦pªG,·¨ÄR&·¨±Ó ¬Ý¨ì¤F¤]½Ð©p©M§ÚÁpµ¸


TEL:0769-2053911 ±M½u
mobile: 13612662102 ½Ðµo«H®§
E-maill: (e-mail address removed)
(e-mail address removed)


TKS!!!

·¨¬îµØ
2004/08/18

 

[Brendan Reynolds]

That seems to be a fair assessment of the problem to me, Boris.

My impression is that use of the form's Recordset property is quite rare. It
was a new feature in Access 2000, and my impression is that most developers
continued manipulating the RecordSource property as they were accustomed to
doing in earlier versions. That's what I do. To test whether a form is
unbound, I would test the length of the RecordSource property ...

If Len(Me.RecordSource) > 0 Then

--
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.

 

[Brendan Reynolds]

While researching a different problem, I came accross the following in the
help files ...

Calling the Requery method of a form's recordset (for example,
Forms(0).Recordset.Requery) can cause the form to become unbound. To refresh
the data in a form bound to a recordset, set the RecordSource property of
the form to itself (Forms(0).RecordSource = Forms(0).RecordSource).

Now I know this isn't the same as your problem, but it looks as though it
could be somehow related, so just out of curiosity I tried changing the code
in your original example (the first MDB you posted) as follows ...

Private Sub cmdButton_Click()
Dim adoCmd As New ADODB.Command

adoCmd.ActiveConnection = CurrentProject.Connection
adoCmd.CommandType = adCmdText

adoCmd.CommandText = "INSERT INTO tblB ([ID], [fkA]) VALUES(1, 1)"
adoCmd.Execute Options:=adExecuteNoRecords

'Me![subform].Requery
Me!subform.Form.RecordSource = Me!subform.Form.RecordSource

adoCmd.CommandText = "DELETE FROM tblB WHERE [ID] = 1"
adoCmd.Execute Options:=adExecuteNoRecords

'Me![subform].Requery
Me!subform.Form.RecordSource = Me!subform.Form.RecordSource

End Sub

This seems to work.

--
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.

 

[david epsom dot com dot au]

but both message boxes show "False" rather than "True".

Brendan,
Just to be clear, you tested with a bound or an unbound form?

(david)

I tested the same code in Access 2003. It doesn't cause any error either,
but both message boxes show "False" rather than "True".

--
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.

I don't have that problem with this in Access 2000:

Private Sub Form_Current()
MsgBox Me.Recordset Is Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
MsgBox Me.Recordset Is Nothing
End Sub

Both msgbox show 'True'

(david)

It took me some hours to track this bug so I hope someone can help or
confirm that this is a bug in Microsoft Access (I use Access 2003). My
database is able to create runtime error 3021 with an empty "If Me.RecordSet
Is Nothing Then" clause. The whole database consists of two tables, two
forms and two short VBA subs. The description of the database follows. If
someone wants to get my database (170 KByte unzipped) please send an e-mail
to boris @ highscore . de (remove spaces) - I don't know if it is
appreciated if databases are sent to these newsgroups.

Okay, I have two simple tables tblA and tblB:

tblA: ID (primary key, long integer)
tblB: ID (primary key, long integer), fkA (long integer, "foreign key" for
tblA)

Add a record to tblA with ID 1.

Then there are two simple forms frmMain and fsubEmbedded:

frmMain: unbound, one subform (fsubEmbedded), one button (cmdButton)
fsubEmbedded: bound ("SELECT tblA.ID FROM tblA, tblB WHERE tblA.ID=tblB.fkA
AND tblB.ID=1"), one text control (bound to tblA.ID)

When you click on the button in frmMain this VBA code is called (I use ADO
so you may need to set a reference to that library):

Private Sub cmdButton_Click()
Dim adoCmd As New ADODB.Command

adoCmd.ActiveConnection = CurrentProject.Connection
adoCmd.CommandType = adCmdText

adoCmd.CommandText = "INSERT INTO tblB ([ID], [fkA]) VALUES(1, 1)"
adoCmd.Execute Options:=adExecuteNoRecords

Me![subform].Requery

adoCmd.CommandText = "DELETE FROM tblB WHERE [ID] = 1"
adoCmd.Execute Options:=adExecuteNoRecords

Me![subform].Requery
End Sub

Everything works perfectly until you add this VBA code to fsubEmbedded:

Private Sub Form_Current()
If Me.Recordset Is Nothing Then
End If
End Sub

Open frmMain, click on the button, close the form - voila, runtime error
3021.

Of course this is the stripped down version of another much bigger database.
So a solution like "don't use that if-then-clause" doesn't help me. If
someone knows what exactly causes this runtime error 3021 I might find a
solution in my database how to prevent it.

Thanks in advance for any ideas,
Boris

 

[david epsom dot com dot au]

.... failure to reset BOF/EOF is a problem in all versions
of Access. I don't think that anyone has ever suggested
that delete or requery would reset BOF/EOF.

Synchronising recordsets is a problem in all versions of
Access. Your first code example showed a delete on the
Active connection: this is virtually guaranteed to lead
to complex synchronisation and locking problems on any
forms bound to the same tables.

(david)

 

[Boris]

... failure to reset BOF/EOF is a problem in all versions
of Access. I don't think that anyone has ever suggested
that delete or requery would reset BOF/EOF.

I didn't find the DAO documentation for Visual Basic at MSDN but the one for
Visual C++:
"If both IsBOF and IsEOF return nonzero after you call Requery, the query
didn't return any records and the recordset will contain no data."
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vcmfc98/ht
ml/_mfc_cdaorecordset.3a3a.requery.asp)

In my test database we call Requery, get a Recordset with 0 records but BOF
and EOF are still false. However I don't know if above statement is true for
ADO, too.

Synchronising recordsets is a problem in all versions of
Access. Your first code example showed a delete on the
Active connection: this is virtually guaranteed to lead
to complex synchronisation and locking problems on any
forms bound to the same tables.

Then according to the DAO documentation and to your statement this is
clearly a bug. As we know now how to fix the bug - Requery simply forgets to
update BOF and EOF if the Recordset is empty - someone should tell the
Microsoft Access team somehow.

Boris

[...]

 

[Brendan Reynolds]

Ah, of course. The form was bound, if I do the same thing in an unbound
form, then I get the same result as you, David, both message boxes show
"True".

--
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.

but both message boxes show "False" rather than "True".

Brendan,
Just to be clear, you tested with a bound or an unbound form?

(david)

I tested the same code in Access 2003. It doesn't cause any error either,
but both message boxes show "False" rather than "True".

--
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.

I don't have that problem with this in Access 2000:

Private Sub Form_Current()
MsgBox Me.Recordset Is Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
MsgBox Me.Recordset Is Nothing
End Sub

Both msgbox show 'True'

(david)


It took me some hours to track this bug so I hope someone can help or
confirm that this is a bug in Microsoft Access (I use Access 2003). My
database is able to create runtime error 3021 with an empty "If
Me.RecordSet
Is Nothing Then" clause. The whole database consists of two tables, two
forms and two short VBA subs. The description of the database

follows.
If

someone wants to get my database (170 KByte unzipped) please send an
e-mail
to boris @ highscore . de (remove spaces) - I don't know if it is
appreciated if databases are sent to these newsgroups.

Okay, I have two simple tables tblA and tblB:

tblA: ID (primary key, long integer)
tblB: ID (primary key, long integer), fkA (long integer, "foreign

key"
for

tblA)

Add a record to tblA with ID 1.

Then there are two simple forms frmMain and fsubEmbedded:

frmMain: unbound, one subform (fsubEmbedded), one button (cmdButton)
fsubEmbedded: bound ("SELECT tblA.ID FROM tblA, tblB WHERE
tblA.ID=tblB.fkA
AND tblB.ID=1"), one text control (bound to tblA.ID)

When you click on the button in frmMain this VBA code is called (I

use
ADO

so you may need to set a reference to that library):

Private Sub cmdButton_Click()
Dim adoCmd As New ADODB.Command

adoCmd.ActiveConnection = CurrentProject.Connection
adoCmd.CommandType = adCmdText

adoCmd.CommandText = "INSERT INTO tblB ([ID], [fkA]) VALUES(1, 1)"
adoCmd.Execute Options:=adExecuteNoRecords

Me![subform].Requery

adoCmd.CommandText = "DELETE FROM tblB WHERE [ID] = 1"
adoCmd.Execute Options:=adExecuteNoRecords

Me![subform].Requery
End Sub

Everything works perfectly until you add this VBA code to fsubEmbedded:

Private Sub Form_Current()
If Me.Recordset Is Nothing Then
End If
End Sub

Open frmMain, click on the button, close the form - voila, runtime error
3021.

Of course this is the stripped down version of another much bigger
database.
So a solution like "don't use that if-then-clause" doesn't help me. If
someone knows what exactly causes this runtime error 3021 I might

find

a

 

[Boris]

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/default.asp?url=/library/en-us/ado270/htm/mdmthadorequery.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/default.asp?url=/library/en-us/ado270/htm/mdmthrstopen.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]

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.

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/default.asp?url=/library/en-us/ado270/htm/mdmthadorequery.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/default.asp?url=/library/en-us/ado270/htm/mdmthrstopen.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

[...]

 

[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.

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

Boris

[...]

 

[david epsom dot com dot au]

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)

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/default.asp?url=/library/en-us/ado270/htm/mdmthadorequery.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/default.asp?url=/library/en-us/ado270/htm/mdmthrstopen.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

[...]

 

[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.


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

[...]

 

[Douglas J. Steele]

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.

 

[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

 

[david epsom dot com dot au]

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)

 

[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).

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

[...]

 

[david epsom dot com dot au]

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)

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

[...]

 

[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."

Oh, that's quite specific. If the DAO360 help is correct the help files for
ADO and DAO about Requery should be changed.

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 ???

If you run the respective VBA code in the test database, wait a minute and
then close the form you still get a runtime error. Is that what you mean by
latency problem?
Do you have a link to sample code explaining some of your unresolved issues
with requery/BOF/EOF in detail? If I see some code I might be able to answer
the question if my problem belongs to the same category or is a new one.

Boris

[...]