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

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

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.

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

  1. Press CTRL+ALT+DEL.
  2. Click Microsoft Access and click End Task.
  3. When the confirmation dialog box appears, click End Task again.

  For Microsoft Windows NT 4.0 or Microsoft Windows 2000

  1. Press CTRL+ALT+DEL.
  2. Click Task Manager.
  3. On the Applications tab, click Microsoft Access, and then click End Task.
  4. When the confirmation dialog box appears, click End Task again.
• 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.
  
  
  1. Open the subform that caused Access to hang in Design view.
  2. On the View menu, click Code.
  3. Type the following line in the Declarations section:

     Dim boolDelete As Boolean
     							

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

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

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

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.

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.