A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #11  
Old August 19th, 2004, 11:21 AM
Boris
external usenet poster
 
Posts: n/a
Default

david epsom dot com dot au wrote:
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'


I don't have any problem with "Me.Recordset Is Nothing" either. What I am
trying to say is that a check of "Me.Recordset Is Nothing" - which works
perfectly - causes a runtime error when the form is closed. If you don't
believe please open the database I attached to my posting yesterday.
Meanwhile I tested this database on another computer with Access 2002, and I
get the runtime error again (I can't test it in Access 2000 as I don't have
this version at hand). As a "Me.Recordset Is Nothing" check in
Form_Current() shouldn't cause a runtime error when the form is closed I
believe this is a bug in Access. The work-around would be to ignore runtime
error 3021 but then I should file a bug report to Microsoft at least.

I am trying to track down the bug some more but would appreciate any help.
Please open the test database, open the main form, click on the button and
then close the main form. If you get a runtime error you see what I mean.
:-)

Boris

"Boris" wrote in message
...
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



  #12  
Old August 19th, 2004, 11:22 AM
Brendan Reynolds
external usenet poster
 
Posts: n/a
Default

I wouldn't usually do this, but curiosity got the better of me, and I took a
look at the MDB you posted. I noticed that the error doesn't occur if I
open and close the form without clicking the command button. It occurs only
after clicking the button, the code behind which writes a record to a table
and then deletes that same record. This leads me to suspect that we're
seeing some kind of timing or caching problem. If this were DAO, I'd be
thinking about putting in a couple of DbEngine.Idle statements, one after
the line that writes the record and another after the line that deletes it.
But I don't know what, if anything, the ADO equivalent of DbEngine.Idle
might be?

--
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
...
Dan Artuso wrote:
Hi,
Okay, 3021 is "no current record".
I'm still not understanding why you are testing for Nothing?
I mean, even if there are no records the form will still have a
recordset, no?
It will just not contain any records.

What is the purpose of the Is Nothing check?
Can you just trap for 3021 and ignore it?


Yes, I could just ignore 3021. However I try to understand where this
runtime error comes from as it might be caused because of other problems -

I
don't know. As I can reproduce this runtime error I try to find the reason
for it.

In my test database the "Is Nothing" check is of no use of course. However
this check causes runtime error 3021 somehow when the form is closed. The
"Is Nothing" check shouldn't do anything, should it? When I comment this
check I don't get any runtime error any more.

The code I sent in my first posting is just the stripped down version of

my
production database. In my production database there is of course much

more
going on in Form_Current(). I have to check the Recordset for Nothing in

my
production database as the subform's Recordset is set dynamically by the
embedding form. And as subforms are loaded first the code in

Form_Current()
must not be executed until the embedding form has set the subform's
Recordset.

However all that really doesn't matter as a check for "Me.Recordset Is
Nothing" shouldn't cause runtime error 3021 when the subform is closed?

Before I try even more to explain I attach the test database in a zip file
to this posting. When you open the database the main form is started
automatically. Click on the button and then close the main form - I get
runtime error 3021 then. When you remove the "Recordset Is Nothing" check
the runtime error disappears. If I know that this is a bug in Access I

will
just ignore 3021. However I would appreciate a confirmation of this bug as
otherwise my code could be wrong of course, too.

Thanks for your help by the way! :-)

Boris

"Boris" wrote in message
...
Dan Artuso wrote:
Hi,
Is Nothing is used to see if an Object variable is 'Set' or not.
It's not used to see if a recordset is empty or not.
I usually use it like this:

ExitHe
If Not rs Is Nothing Then
Set rs = Nothing
End If

To see if your recordset is empty, get the record count if possible,
or check the EOF and BOF properties.

Thanks for your answer. However that's not the problem. The
production database uses code likes this:

If Not Me.Recordset Is Nothing Then
If Not (Me.Recordset.BOF Or Me.Recordset.EOF) Then
...
End If
End If

Checking for empty recordsets works without any problems. The
problem is that it causes a runtime error 3021 when you close the
main form. I stripped the code as much as possible for my test
database to track the bug. For runtime error 3021 it just matters
that you test "Me.RecordSet Is Nothing". I don't do anything else in
Form_Current() in my test database but still get runtime error 3021.
If I comment the if-then-clause the runtime error strangely
disappears?

Boris

[...]






  #13  
Old August 19th, 2004, 12:02 PM
Boris
external usenet poster
 
Posts: n/a
Default

Brendan Reynolds wrote:
I wouldn't usually do this, but curiosity got the better of me, and I
took a look at the MDB you posted. I noticed that the error doesn't
occur if I open and close the form without clicking the command
button. It occurs only after clicking the button, the code behind
which writes a record to a table and then deletes that same record.
This leads me to suspect that we're seeing some kind of timing or
caching problem. If this were DAO, I'd be thinking about putting in a
couple of DbEngine.Idle statements, one after the line that writes
the record and another after the line that deletes it. But I don't
know what, if anything, the ADO equivalent of DbEngine.Idle might be?


Hi, thanks for jumping into the thread! I will rewrite the code using DAO.
Maybe this is an ADO problem. I will be back soon.

Boris

[...]



  #14  
Old August 19th, 2004, 12:42 PM
Brendan Reynolds
external usenet poster
 
Posts: n/a
Default

Well, you've got my curiositly going now! So I tried changing the code
behind the command button as follows, (I've left the original ADO code in
there, but commented out) and sure enough the error disappears ...

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
CurrentDb.Execute "INSERT INTO tblB (ID, fkA) VALUES (1, 1)",
dbFailOnError
Me![subform].Requery

'adoCmd.CommandText = "DELETE FROM tblB WHERE [ID] = 1"
'adoCmd.Execute Options:=adExecuteNoRecords
CurrentDb.Execute "DELETE FROM tblB WHERE ID = 1", dbFailOnError
Me![subform].Requery
End Sub

--
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
...
Brendan Reynolds wrote:
I wouldn't usually do this, but curiosity got the better of me, and I
took a look at the MDB you posted. I noticed that the error doesn't
occur if I open and close the form without clicking the command
button. It occurs only after clicking the button, the code behind
which writes a record to a table and then deletes that same record.
This leads me to suspect that we're seeing some kind of timing or
caching problem. If this were DAO, I'd be thinking about putting in a
couple of DbEngine.Idle statements, one after the line that writes
the record and another after the line that deletes it. But I don't
know what, if anything, the ADO equivalent of DbEngine.Idle might be?


Hi, thanks for jumping into the thread! I will rewrite the code using DAO.
Maybe this is an ADO problem. I will be back soon.

Boris

[...]





  #15  
Old August 19th, 2004, 12:50 PM
Boris
external usenet poster
 
Posts: n/a
Default

Brendan Reynolds wrote:
Well, you've got my curiositly going now! So I tried changing the code
behind the command button as follows, (I've left the original ADO
code in there, but commented out) and sure enough the error
disappears ...

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
CurrentDb.Execute "INSERT INTO tblB (ID, fkA) VALUES (1, 1)",
dbFailOnError
Me![subform].Requery

'adoCmd.CommandText = "DELETE FROM tblB WHERE [ID] = 1"
'adoCmd.Execute Options:=adExecuteNoRecords
CurrentDb.Execute "DELETE FROM tblB WHERE ID = 1", dbFailOnError
Me![subform].Requery
End Sub


The error also disappears when you use the ADO code and comment one of the
two Me![subform].Requery lines.
Still trying to figure out what goes wrong ...

Boris

[...]



  #16  
Old August 19th, 2004, 01:04 PM
Boris
external usenet poster
 
Posts: n/a
Default

Boris wrote:
Brendan Reynolds wrote:
I wouldn't usually do this, but curiosity got the better of me, and I
took a look at the MDB you posted. I noticed that the error doesn't
occur if I open and close the form without clicking the command
button. It occurs only after clicking the button, the code behind
which writes a record to a table and then deletes that same record.
This leads me to suspect that we're seeing some kind of timing or
caching problem. If this were DAO, I'd be thinking about putting in a
couple of DbEngine.Idle statements, one after the line that writes
the record and another after the line that deletes it. But I don't
know what, if anything, the ADO equivalent of DbEngine.Idle might be?


Hi, thanks for jumping into the thread! I will rewrite the code using
DAO. Maybe this is an ADO problem. I will be back soon.


I'm back: I didn't change the code but removed one table and the subform.
Now there is just one table and just one form - the runtime error persists.
See the attachment please.

Boris




Attached Files
File Type: zip Runtime Error 3021 (2).zip (13.1 KB, 43 views)
  #17  
Old August 19th, 2004, 01:11 PM
Brendan Reynolds
external usenet poster
 
Posts: n/a
Default

In the real world, Boris, you're hardly going to be adding a record and then
immediately deleting it again? There doesn't seem to be much point in
investigating that particular situation much further, because it doesn't
seem to be a realistic situation - what works in that artificial situation
may not work under real world conditions. I understand your desire to
simplify, but perhaps we need to get a bit closer to the real-world
situation in order to make any further progress?

--
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
...
Brendan Reynolds wrote:
Well, you've got my curiositly going now! So I tried changing the code
behind the command button as follows, (I've left the original ADO
code in there, but commented out) and sure enough the error
disappears ...

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
CurrentDb.Execute "INSERT INTO tblB (ID, fkA) VALUES (1, 1)",
dbFailOnError
Me![subform].Requery

'adoCmd.CommandText = "DELETE FROM tblB WHERE [ID] = 1"
'adoCmd.Execute Options:=adExecuteNoRecords
CurrentDb.Execute "DELETE FROM tblB WHERE ID = 1", dbFailOnError
Me![subform].Requery
End Sub


The error also disappears when you use the ADO code and comment one of the
two Me![subform].Requery lines.
Still trying to figure out what goes wrong ...

Boris

[...]





  #18  
Old August 19th, 2004, 01:22 PM
Boris
external usenet poster
 
Posts: n/a
Default

Brendan Reynolds wrote:
Well, you've got my curiositly going now! So I tried changing the code
behind the command button as follows, (I've left the original ADO
code in there, but commented out) and sure enough the error
disappears ...


Brendan, I just tried your code and still get runtime error 3021! Replacing
ADO code with CurrentDb.Execute calls doesn't help me. Which Access version
do you use and which references did you set?

Boris

[...]



  #19  
Old August 19th, 2004, 01:41 PM
Brendan Reynolds
external usenet poster
 
Posts: n/a
Default

See my previous response, Boris. This demonstrates that problems can arise
if you add a record and then immediately delete that same record. But why
does it matter? Why would anyone want to do that?

BTW: Does anyone know if ADO does have an equivalent to DAO's DbEngine.Idle?

--
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
...
Boris wrote:
Brendan Reynolds wrote:
I wouldn't usually do this, but curiosity got the better of me, and I
took a look at the MDB you posted. I noticed that the error doesn't
occur if I open and close the form without clicking the command
button. It occurs only after clicking the button, the code behind
which writes a record to a table and then deletes that same record.
This leads me to suspect that we're seeing some kind of timing or
caching problem. If this were DAO, I'd be thinking about putting in a
couple of DbEngine.Idle statements, one after the line that writes
the record and another after the line that deletes it. But I don't
know what, if anything, the ADO equivalent of DbEngine.Idle might be?


Hi, thanks for jumping into the thread! I will rewrite the code using
DAO. Maybe this is an ADO problem. I will be back soon.


I'm back: I didn't change the code but removed one table and the subform.
Now there is just one table and just one form - the runtime error

persists.
See the attachment please.

Boris





  #20  
Old August 19th, 2004, 02:00 PM
Brendan Reynolds
external usenet poster
 
Posts: n/a
Default

Access 2003. I removed all references except the Access, VBA, and DAO
references, as these were the only references needed once I commented out
the ADO code. I didn't remove the other references for any particular reason
other than habit - I'm just in the habit of always removing any reference
I'm not using.

--
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
...
Brendan Reynolds wrote:
Well, you've got my curiositly going now! So I tried changing the code
behind the command button as follows, (I've left the original ADO
code in there, but commented out) and sure enough the error
disappears ...


Brendan, I just tried your code and still get runtime error 3021!

Replacing
ADO code with CurrentDb.Execute calls doesn't help me. Which Access

version
do you use and which references did you set?

Boris

[...]





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Useless Access 2003 tired, angry, sucidial and bored General Discussion 10 July 21st, 2004 11:52 PM
Access 2003 Runtime Issue IraKeener General Discussion 0 June 30th, 2004 08:42 PM
Access 2003 RK General Discussion 12 June 14th, 2004 10:16 AM
Problem running Access 2003 and Access 2000 apps on same machine. Rathtap General Discussion 3 June 13th, 2004 01:30 AM
Productkey problem when installing office 2003 on network Stefan Schreurs Setup, Installing & Configuration 1 June 1st, 2004 11:16 PM


All times are GMT +1. The time now is 06:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.