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

• Microsoft Access 2000

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

• If Access is not responding, follow these steps, depending on which operating system you have.

  For Windows 95 or 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 Windows NT 4.0

  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 

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

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 : kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug