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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 [...] |
#8
|
|||
|
|||
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 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
|
|||
|
|||
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 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
|
|||
|
|||
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 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 | |
|
|
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 |