ACC2002: Error Message: Operation Not Supported in Transactions (295229)

RESOLUTION

To work around this behavior, instead of using the Form_Delete event to customize how records are deleted, use the Form_BeforeDelConfirm event. To see an example of how to do this, use the following steps to resolve the problem that is described in the "Steps to Reproduce the Behavior" section later in this article.

Comment out all lines of code within the Form_Delete event procedure, or delete the Form_Delete event procedure from the module of the form.

Copy and paste the following code into the module of the form:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)

'Message box to prompt for confirmation cancelling delete operation
If MsgBox("Delete current record ?", vbExclamation + vbOKCancel) = vbCancel Then 
Cancel = True
End If

'Enables Microsoft Access to delete records without prompting the user.
Response = acDataErrContinue

End Sub

MORE INFORMATION

In Microsoft Access 97, setting the Cancel argument equal to True in the Form_Delete event of a subform does not break the link with any related subform control.

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.

Steps to Reproduce the Behavior

Start Access, open the Northwind sample database, and then copy and paste the Orders table into a table named SubA and the Order Details table into a table named SubB.
Using the AutoForm: Tabular Wizard, create a form that is based on the SubA table, save the form as SubA, and then close it.
Using the AutoForm: Tabular Wizard, create a form that is based on the SubB table, save the form as SubB, and then save it.
Open the SubA form in Design view, and then click Code on the View menu to open the Visual Basic Editor.
Type the following lines into the module of the form if they are not already there:
```
Option Compare Database
Option Explicit
					
```

Copy and paste the following code:

Dim OldOptVal As Integer

Private Sub Form_Close()
'Set Confirm Record Changes confirmation back to original state
SetOption "Confirm Record Changes", OldOptVal
End Sub

Private Sub Form_Current()
' Assign to the control "OrderID" on the Main form the current OrderID on the subform
Me.Parent!txtOrderID = Me!OrderID

End Sub

Private Sub Form_Delete(Cancel As Integer)
'Message box to prompt for confirmation cancelling delete operation
If MsgBox("Delete current record ?", vbExclamation + vbOKCancel) = vbCancel Then
Cancel = True
End If

End Sub

Private Sub Form_Open(Cancel As Integer)
'Get the current setting of Confirm Record Changes
OldOptVal = GetOption("Confirm Record Changes")
'Set the Confirm Record Changes setting to True
SetOption "Confirm Record Changes", True

End Sub

Close and then save the form.
Create a blank form in Design view, and then add an unbound text box control named txtOrderID.

With the SubForm/SubReport Wizard turned off, create two subform controls with the following properties

Name:               ctlSubA     ctlSubB
SourceObject:       SubA        SubB
Link Child Fields:  (unbound)   OrderID
Link Master Fields: (unbound)   txtOrderID

Close and save the form as Main, and then reopen the form in Form view.
Click the first record in the SubA subform, press DELETE, and then click Cancel. Note that the link to the SubB subform is broken at this point
In the SubA subform, move the record navigation selector to the next record. Note that you receive the following error message:
Operation not supported in transactions