Microsoft KB Archive/295229

= ACC2002: Error Message: Operation Not Supported in Transactions =

Article ID: 295229

Article Last Modified on 9/26/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q295229



Advanced: Requires expert coding, interoperability, and multiuser skills.

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



SYMPTOMS
If you set the Cancel argument equal to True in the Form_Delete event of a subform, the link to any related subform control is broken. If you then try to move to another record within the subform, you may receive the following error message:

Operation not supported in transactions.



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 &quot;Steps to Reproduce the Behavior&quot; 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(&quot;Delete current record ?&quot;, vbExclamation + vbOKCancel) = vbCancel Then Cancel = True End If

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

End Sub 



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



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.</li> Using the AutoForm: Tabular Wizard, create a form that is based on the SubB table, save the form as SubB, and then save it.</li> Open the SubA form in Design view, and then click Code on the View menu to open the Visual Basic Editor.</li>  Type the following lines into the module of the form if they are not already there: Option Compare Database Option Explicit </li>  Copy and paste the following code: Dim OldOptVal As Integer

Private Sub Form_Close 'Set Confirm Record Changes confirmation back to original state SetOption &quot;Confirm Record Changes&quot;, OldOptVal End Sub

Private Sub Form_Current ' Assign to the control &quot;OrderID&quot; 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(&quot;Delete current record ?&quot;, 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(&quot;Confirm Record Changes&quot;) 'Set the Confirm Record Changes setting to True SetOption &quot;Confirm Record Changes&quot;, True

End Sub </li> Close and then save the form.</li> Create a blank form in Design view, and then add an unbound text box control named txtOrderID.</li> With the SubForm/SubReport Wizard turned off, create two subform controls with the following properties

<pre class="fixed_text">Name:              ctlSubA     ctlSubB SourceObject:      SubA        SubB Link Child Fields: (unbound)   OrderID Link Master Fields: (unbound)  txtOrderID

</li> Close and save the form as Main, and then reopen the form in Form view.</li> 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</li> 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

</li></ol>

Additional query words: synchronize subforms link subform pra

Keywords: kberrmsg kbbug kbpending KB295229

-

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

© Microsoft Corporation. All rights reserved.