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

[Boris]

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

 

[Roger Carlson]

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.

 

[Dan Artuso]

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:

ExitHere:
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]

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

 

[Boris]

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:

ExitHere:
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

[...]

 

[Dan Artuso]

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

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:

ExitHere:
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

[...]

 

[david epsom dot com dot au]

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)

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

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]

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)

 

[Brendan Reynolds]

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

 

[Boris]

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

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

 

[Brendan Reynolds]

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.

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

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:

ExitHere:
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

[...]

 

[Boris]

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

[...]

 

[Brendan Reynolds]

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.

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

[...]

 

[Boris]

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

[...]

 

[Brendan Reynolds]

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.

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

[...]

 

[Boris]

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

[...]

 

[Brendan Reynolds]

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.

 

[Brendan Reynolds]

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.

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

[...]

 

[Brendan Reynolds]

Hmm. That's odd. Your third example does, as you say, raise the error if you
comment out the line that explicitly calls the Form_Current event. But I
just went back and double-checked my modified version of your first example,
which I changed to use DAO instead of ADO, and yes that still works without
error without explicitly calling the Form_Current event. But then, things
have changed since then, that was back when there was still a subform
involved.

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

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?

In my production database I have two buttons: One to add new records to a
table, another one to remove records again. You can happily add and remove
records without any problems. However whenever I removed all records and
closed the form I got this runtime error 3021. For the runtime error to
appear it is just important that all existing records are deleted. Put a
record into the table yourself and comment the INSERT-part in VBA - there is
still a runtime error.

I believe the problem is this: When the form is opened and one record is
inserted the first call to Requery executes Form_Current(). Accessing
Me.Recordset is enough for the form to see that there is now one record
(even if it is just a "if-then-clause"). When the record is then deleted and
Requery is called the second time Form_Current() is *not* executed. You see
this when you step through the code. Now the form believes there is still
one record. When the form is closed it tries to save this record. If you add
this code to the form you see that RecordCount is 0 but Recordset.BOF and
Recordset.EOF are both false:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Debug.Print "RecordCount: " & Me.Recordset.RecordCount
Debug.Print "BOF: " & Me.Recordset.BOF
Debug.Print "EOF: " & Me.Recordset.EOF
End Sub

The second Requery doesn't execute Form_Current(), and that's part of the
problem. You have to call Form_Current() yourself and force Access to
reconsider the RecordSet. Add a call to Form_Current() after the second
Requery and change Form_Current() to:

Public Sub Form_Current()
If Not Me.Recordset Is Nothing Then
Me.Recordset.MoveFirst
End If
End Sub

I attached the test database with these changes. Now it works without any
runtime error.

Boris

[...]

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

[...]

 

[Boris]

I changed now the VBA code to use DAO (deleting the record with
"Me.Recordset.Delete") but that doesn't help. The runtime error persists no
matter how you delete the record.

Conclusion: If you use VBA to delete all records in a recordset that is
bound to a form RecordCount is updated but BOF and EOF are not! Calling
Requery doesn't help. When the form is then closed it tries to save the
records that don't exist any more - runtime error 3021.

Work-around: You have to force the bound form to reconsider the Recordset
after all records have been deleted (eg. by calling MoveFirst). This updates
BOF and EOF, and the form will not try to save records any more when it is
closed.

Now is this a bug in Access? I understand that a form doesn't know when
records are added or deleted in VBA. However I thought calling Requery makes
the form know?

Boris