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 |
#21
|
|||
|
|||
Brendan Reynolds wrote:
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 [...] "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 [...] |
#22
|
|||
|
|||
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. "Boris" wrote in message ... Brendan Reynolds wrote: 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 [...] "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 [...] |
#23
|
|||
|
|||
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 |
#24
|
|||
|
|||
That seems to be a fair assessment of the problem to me, Boris.
My impression is that use of the form's Recordset property is quite rare. It was a new feature in Access 2000, and my impression is that most developers continued manipulating the RecordSource property as they were accustomed to doing in earlier versions. That's what I do. To test whether a form is unbound, I would test the length of the RecordSource property ... If Len(Me.RecordSource) 0 Then -- 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 ... 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 |
#25
|
|||
|
|||
While researching a different problem, I came accross the following in the
help files ... Calling the Requery method of a form's recordset (for example, Forms(0).Recordset.Requery) can cause the form to become unbound. To refresh the data in a form bound to a recordset, set the RecordSource property of the form to itself (Forms(0).RecordSource = Forms(0).RecordSource). Now I know this isn't the same as your problem, but it looks as though it could be somehow related, so just out of curiosity I tried changing the code in your original example (the first MDB you posted) as follows ... 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 Me!subform.Form.RecordSource = Me!subform.Form.RecordSource adoCmd.CommandText = "DELETE FROM tblB WHERE [ID] = 1" adoCmd.Execute Options:=adExecuteNoRecords 'Me![subform].Requery Me!subform.Form.RecordSource = Me!subform.Form.RecordSource End Sub This seems to work. -- 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" brenreyn at indigo dot ie wrote in message ... That seems to be a fair assessment of the problem to me, Boris. My impression is that use of the form's Recordset property is quite rare. It was a new feature in Access 2000, and my impression is that most developers continued manipulating the RecordSource property as they were accustomed to doing in earlier versions. That's what I do. To test whether a form is unbound, I would test the length of the RecordSource property ... If Len(Me.RecordSource) 0 Then -- 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 ... 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 |
#26
|
|||
|
|||
Brendan Reynolds wrote:
That seems to be a fair assessment of the problem to me, Boris. My impression is that use of the form's Recordset property is quite rare. It was a new feature in Access 2000, and my impression is that most developers continued manipulating the RecordSource property as they were accustomed to doing in earlier versions. That's what I do. To test whether a form is unbound, I would test the length of the RecordSource property ... If Len(Me.RecordSource) 0 Then As far as I understand this problem isn't caused by using Recordset in general. I think it is a synchronization problem between the Recordset and the form. In my opinion the reason for this runtime error is that a call to Requery doesn't synchronize a bound form correctly when the Recordset is empty. While RecordCount seems to be updated, BOF and EOF are not. To avoid the runtime error you have to update BOF and EOF yourself if the Recordset is empty. That leads to an easy work-around: Whenever you delete a Record in VBA and the Recordset is bound to a form add this line after the call to Requery: If Me.Recordset.RecordCount = 0 Then Me.Recordset.MoveFirst This guarantees that BOF and EOF are updated, too, and have the value they really should have when the form is closed. I attached the test database which proves that the work-around works. Boris [...] |
#27
|
|||
|
|||
but both message boxes show "False" rather than "True".
Brendan, Just to be clear, you tested with a bound or an unbound form? (david) "Brendan Reynolds" brenreyn at indigo dot ie wrote in message ... 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 |
#28
|
|||
|
|||
.... failure to reset BOF/EOF is a problem in all versions
of Access. I don't think that anyone has ever suggested that delete or requery would reset BOF/EOF. Synchronising recordsets is a problem in all versions of Access. Your first code example showed a delete on the Active connection: this is virtually guaranteed to lead to complex synchronisation and locking problems on any forms bound to the same tables. (david) "Boris" wrote in message ... 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 |
#29
|
|||
|
|||
david epsom dot com dot au wrote:
... failure to reset BOF/EOF is a problem in all versions of Access. I don't think that anyone has ever suggested that delete or requery would reset BOF/EOF. I didn't find the DAO documentation for Visual Basic at MSDN but the one for Visual C++: "If both IsBOF and IsEOF return nonzero after you call Requery, the query didn't return any records and the recordset will contain no data." (http://msdn.microsoft.com/library/de...-us/vcmfc98/ht ml/_mfc_cdaorecordset.3a3a.requery.asp) In my test database we call Requery, get a Recordset with 0 records but BOF and EOF are still false. However I don't know if above statement is true for ADO, too. Synchronising recordsets is a problem in all versions of Access. Your first code example showed a delete on the Active connection: this is virtually guaranteed to lead to complex synchronisation and locking problems on any forms bound to the same tables. Then according to the DAO documentation and to your statement this is clearly a bug. As we know now how to fix the bug - Requery simply forgets to update BOF and EOF if the Recordset is empty - someone should tell the Microsoft Access team somehow. Boris [...] |
#30
|
|||
|
|||
Ah, of course. The form was bound, if I do the same thing in an unbound
form, then I get the same result as you, David, both message boxes show "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 ... but both message boxes show "False" rather than "True". Brendan, Just to be clear, you tested with a bound or an unbound form? (david) "Brendan Reynolds" brenreyn at indigo dot ie wrote in message ... 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 |