Microsoft KB Archive/239549

= ACC2000: Access Hangs When You Delete a Record on a Subform That You Created with the Form Wizard =

Article ID: 239549

Article Last Modified on 11/25/2002

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q239549



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
When you delete a record from a subform that was built with the Form wizard, Microsoft Access hangs (stops responding).



CAUSE
You experience this problem when you use the Form wizard to create a form that has two related subforms and then delete a record from the first subform. When you delete a record from the primary subform, Access enters an infinite loop trying to requery the second subform.



RESOLUTION
To resolve this problem, obtain Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a).

To obtain SR-1/SR-1a, click the article number below to view the article in the Microsoft Knowledge Base:

245025 OFF2000: How to Obtain and Install Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a)

To temporarily work around this problem, do the following, depending on what your situation is:   If Access is not responding, follow these steps, depending on which operating system you have.

For Microsoft Windows 95 or Microsoft Windows 98
 Press CTRL+ALT+DEL. Click Microsoft Access and click End Task. When the confirmation dialog box appears, click End Task again.

For Microsoft Windows NT 4.0 or Microsoft Windows 2000
 Press CTRL+ALT+DEL.</li> Click Task Manager.</li> On the Applications tab, click Microsoft Access, and then click End Task.</li> When the confirmation dialog box appears, click End Task again.</li></ol> </li>  To prevent this problem from happening again, use one of the following methods:

Method 1
Comment out the code in the Current event of the subform.

NOTE: When you use this method, the second subform does not automatically update to show the records that are related to the selected record in the first subform.

Method 2
Create a variable that contains True or False. When you delete a record from the subform set this variable to True. In the Current event of the first subform check the variable; if it contains True, do not re-query the second subform. Finally, set the variable to False. The following steps show you how to do this.

 Open the subform that caused Access to hang in Design view.</li> On the View menu, click Code.</li>  Type the following line in the Declarations section: Dim boolDelete As Boolean </li>  Add the following code to the Delete event of the subform: Private Sub Form_Delete(Cancel As Integer) ' Set the module level variable to True to  ' indicate a delete. boolDelete = True End Sub </li>  Change the Current event of the subform to the following procedure: Sub Form_Current

On Error Resume Next

If Err <> 0 Then GoTo Form_Current_Exit Else On Error GoTo Form_Current_Err If boolDelete = False Then ' Only Requery the secondary subform in the Current ' event if no delete has taken place. Me.Parent![Order Details Subform].Requery End If  End If

Form_Current_Exit: Exit Sub

Form_Current_Err: MsgBox Error$ Resume Form_Current_Exit

End Sub </li>  Add the following code to the AfterDelConfirm event of the subform: Private Sub Form_AfterDelConfirm(Status As Integer) ' Once the delete is finished, Requery the secondary ' subform, then set the module level variable back ' to False in indicate that no delete has taken place. Me.Parent![Order Details Subform].Requery boolDelete = False End Sub </li></ol> </li></ul>

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was corrected in Microsoft Office 2000 SR-1/SR-1a.

<div class="moreinformation_section">

Steps to Reproduce Behavior
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.


 * 1) Open the sample database Northwind.mdb.
 * 2) Click Forms under Objects, and then click New.
 * 3) In the New Form dialog box, click Form Wizard, and then click OK.
 * 4) In the Tables/Queries list, click Table: Customers.
 * 5) Move CustomerID and CompanyName from the Available Fields list to the Selected Fields list.
 * 6) In the Tables/Queries list, click Table: Orders.
 * 7) Move OrderID, ShipName, and Freight from the Available Fields list to the Selected Fields list.
 * 8) In the Tables/Queries list, click Table: Order Details.
 * 9) Move ProductID, UnitPrice, and Quantity from the Available Fields list to the Selected Fields list, and then click Next.
 * 10) Click Form with subform(s), and then click Finish.
 * 11) Select a record on the Orders subform, and then press DELETE. Note that at this point, Access stops responding.

Additional query words: pra

Keywords: kbbug kbpending KB239549

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.