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 |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
#17
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |