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
  #1  
Old August 18th, 2004, 01:49 PM
Boris
external usenet poster
 
Posts: n/a
Default Bug: Empty "If Me.Recordset Is Nothing Then" clause causes runtime error 3021 (Access 2003)

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


  #2  
Old August 18th, 2004, 02:44 PM
Roger Carlson
external usenet poster
 
Posts: n/a
Default

If I was checking to see if the subform is empty, I'd use:

Private Sub Form_Current()
If Me.RecordSetClone.EOF Then
'do stuff here
End If
End Sub

However, I believe this requires DAO.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"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




  #3  
Old August 18th, 2004, 03:53 PM
Dan Artuso
external usenet poster
 
Posts: n/a
Default

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.

HTH
Dan Artuso, MVP

"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




  #4  
Old August 18th, 2004, 04:12 PM
Boris
external usenet poster
 
Posts: n/a
Default

Roger Carlson wrote:
If I was checking to see if the subform is empty, I'd use:

Private Sub Form_Current()
If Me.RecordSetClone.EOF Then
'do stuff here
End If
End Sub

However, I believe this requires DAO.


Hehe, okay, that fixes my test database. :-)

However I can not use this fix in my production database as the subform's
RecordSource is set dynamically. When Form_Current() of the subform is
called I have to check if Me.RecordSet Is Nothing. Checking if
Me.RecordSetClone Is Nothing results in another runtime error 7951.

I changed the test database. Now it is more similar to my production
database. So unfortunately your fix doesn't work anymore. The subform is now
unbound. The main form sets the subform's RecordSource when it is opened:

Private Sub Form_Open(Cancel As Integer)
Me![subform].Form.RecordSource = "SELECT tblA.ID FROM tblA, tblB WHERE
tblA.ID=tblB.fkA And tblB.ID=1"
End Sub

My question stands: What exactly causes runtime error 3021 when the forms
are closed?

Boris


  #5  
Old August 18th, 2004, 04:23 PM
Boris
external usenet poster
 
Posts: n/a
Default

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

[...]



  #6  
Old August 18th, 2004, 07:31 PM
Dan Artuso
external usenet poster
 
Posts: n/a
Default

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?

Dan Artuso, MVP

"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

[...]





  #7  
Old August 18th, 2004, 08:12 PM
Boris
external usenet poster
 
Posts: n/a
Default

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

[...]





Attached Files
File Type: zip Runtime Error 3021.zip (15.3 KB, 52 views)
  #8  
Old August 19th, 2004, 09:58 AM
david epsom dot com dot au
external usenet poster
 
Posts: n/a
Default

If Me.RecordSetClone.EOF Then
However, I believe this requires DAO.


It does not require a DAO reference. I don't know
if Access will load if DAO is not correctly installed.

(david)


"Roger Carlson" wrote in message
...
If I was checking to see if the subform is empty, I'd use:

Private Sub Form_Current()
If Me.RecordSetClone.EOF Then
'do stuff here
End If
End Sub

However, I believe this requires DAO.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"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






  #9  
Old August 19th, 2004, 10:17 AM
david epsom dot com dot au
external usenet poster
 
Posts: n/a
Default

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)


"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




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

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.


"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
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)


"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






 




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 11:52 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.