Question about error #2074 - "Operation not supported withtransactions"

[EManning]

Using A2003. I'm receiving this error when returning from a
"DoCmd.OpenReport..." statement. I have a tab control with a subform
on every tab. The user selects an item from a combobox at the top of
the main form which refreshes all the subforms. If this DoCmd
statement is not executed, then I don't receive the error. I tried a
"DoCmd.OpenQuery..." instead of a report but get the same error.

I want the user to be able to print-preview a report if they try to
delete an item from any combobox on the subforms where there are
related records. They're given an option to print-preview a report
showing the related records. If they choose to print-preview, then
this error occurs on trying to move to another record. If they choose
not to print-preview, then the error doesn't occur.

It appears to be something with the DoCmd property. I'm finding some
posts on this newsgroup about getting this error after deleting
records and being careful about how subforms are linked. If there are
no related records, then the item is deleted and I'm able to go on to
another record. The subforms refresh just fine if the DoCmd is not
executed.

Any help or advice on how to fix or work-around this problem is
appreciated.

[Tom van Stiphout]

On Thu, 19 Jun 2008 07:18:15 -0700 (PDT), EManning
<ma**********@hotmail.comwrote:

The term "transaction" more than likely refers to a database
transaction. As in a stored procedure with:
BEGIN TRANS
--Do Something
COMMIT TRANS

or the transaction support in DAO or ADO.

Is that what you are doing?

-Tom.

>Using A2003. I'm receiving this error when returning from a
"DoCmd.OpenReport..." statement. I have a tab control with a subform
on every tab. The user selects an item from a combobox at the top of
the main form which refreshes all the subforms. If this DoCmd
statement is not executed, then I don't receive the error. I tried a
"DoCmd.OpenQuery..." instead of a report but get the same error.

I want the user to be able to print-preview a report if they try to
delete an item from any combobox on the subforms where there are
related records. They're given an option to print-preview a report
showing the related records. If they choose to print-preview, then
this error occurs on trying to move to another record. If they choose
not to print-preview, then the error doesn't occur.

It appears to be something with the DoCmd property. I'm finding some
posts on this newsgroup about getting this error after deleting
records and being careful about how subforms are linked. If there are
no related records, then the item is deleted and I'm able to go on to
another record. The subforms refresh just fine if the DoCmd is not
executed.

Any help or advice on how to fix or work-around this problem is
appreciated.

[EManning]

On Jun 19, 10:10*pm, Tom van Stiphout <[e-mail]:

On Thu, 19 Jun 2008 07:18:15 -0700 (PDT), EManning

<[e-mail]:

The term "transaction" more than likely refers to a database
transaction. As in a stored procedure with:
BEGIN TRANS
--Do Something
COMMIT TRANS

or the transaction support in DAO or ADO.

Is that what you are doing?

-Tom.

Using A2003. *I'm receiving this error when returning from a
"DoCmd.OpenReport..." statement. *I have a tab control with a subform
on every tab. *The user selects an item from a combobox at the top of
the main form which refreshes all the subforms. *If this DoCmd
statement is not executed, then I don't receive the error. *I tried a
"DoCmd.OpenQuery..." instead of a report but get the same error.

I want the user to be able to print-preview a report if they try to
delete an item from any combobox on the subforms where there are
related records. *They're given an option to print-preview a report
showing the related records. *If they choose to print-preview, then
this error occurs on trying to move to another record. *If they choose
not to print-preview, then the error doesn't occur.

It appears to be something with the DoCmd property. *I'm finding some
posts on this newsgroup about getting this error after deleting
records and being careful about how subforms are linked. *If there are
no related records, then the item is deleted and I'm able to go on to
another record. *The subforms refresh just fine if the DoCmd is not
executed.

Any help or advice on how to fix or work-around this problem is
appreciated.- Hide quoted text -

- Show quoted text -

Thanks for your reply. No, I'm not implicitly doing a transaction.
Here is the "offending" code...

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Select Case DataErr
Case 3200, 3146
strTitle = "Cannot delete - there are projects assigned
this department"
strMsg = "Would you like to print a list of these
projects?" & vbCrLf & vbCrLf & _
"Click on Yes to print, No to cancel."

If MsgBox(strMsg, vbYesNo, strTitle) = vbYes Then
DoCmd.OpenReport "rpt_tlkpDepartments",
acViewPreview, , , , CInt(Me.DepartmentID)
End If

Response = acDataErrContinue
Case Else

The error does not occur if the user chooses not to print the report.
If they do choose to print, then the error occurs when they try to go
to another project.

According to Help, a transaction can occur like you stated or "or an
internal transaction created by the Microsoft Jet database engine,
such as when you delete multiple records in a single operation.".
This DoCmd is executed because the user tried to delete an item in a
combobox that has related records. But the deletion did not take
place. But internally, Jet may still be in transaction mode?????

[Tom van Stiphout]

On Fri, 20 Jun 2008 06:32:28 -0700 (PDT), EManning
<ma**********@hotmail.comwrote:

Are you doing a delete above this code block? The error message seems
to indicate as much. There could be a transaction involved there.

Select queries never use transactions (implicit or not), or at least
they are never needed, and Access I'm sure won't use them.

-Tom.

>On Jun 19, 10:10*pm, Tom van Stiphout <[e-mail]:

>On Thu, 19 Jun 2008 07:18:15 -0700 (PDT), EManning

<[e-mail]:

The term "transaction" more than likely refers to a database
transaction. As in a stored procedure with:
BEGIN TRANS
--Do Something
COMMIT TRANS

or the transaction support in DAO or ADO.

Is that what you are doing?

-Tom.

>Using A2003. *I'm receiving this error when returning from a
"DoCmd.OpenReport..." statement. *I have a tab control with a subform
on every tab. *The user selects an item from a combobox at the top of
the main form which refreshes all the subforms. *If this DoCmd
statement is not executed, then I don't receive the error. *I tried a
"DoCmd.OpenQuery..." instead of a report but get the same error.

>I want the user to be able to print-preview a report if they try to
delete an item from any combobox on the subforms where there are
related records. *They're given an option to print-preview a report
showing the related records. *If they choose to print-preview, then
this error occurs on trying to move to another record. *If they choose
not to print-preview, then the error doesn't occur.

>It appears to be something with the DoCmd property. *I'm finding some
posts on this newsgroup about getting this error after deleting
records and being careful about how subforms are linked. *If there are
no related records, then the item is deleted and I'm able to go on to
another record. *The subforms refresh just fine if the DoCmd is not
executed.

>Any help or advice on how to fix or work-around this problem is
appreciated.- Hide quoted text -

- Show quoted text -

Thanks for your reply. No, I'm not implicitly doing a transaction.
Here is the "offending" code...

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Select Case DataErr
Case 3200, 3146
strTitle = "Cannot delete - there are projects assigned
this department"
strMsg = "Would you like to print a list of these
projects?" & vbCrLf & vbCrLf & _
"Click on Yes to print, No to cancel."

If MsgBox(strMsg, vbYesNo, strTitle) = vbYes Then
DoCmd.OpenReport "rpt_tlkpDepartments",
acViewPreview, , , , CInt(Me.DepartmentID)
End If

Response = acDataErrContinue
Case Else

The error does not occur if the user chooses not to print the report.
If they do choose to print, then the error occurs when they try to go
to another project.

According to Help, a transaction can occur like you stated or "or an
internal transaction created by the Microsoft Jet database engine,
such as when you delete multiple records in a single operation.".
This DoCmd is executed because the user tried to delete an item in a
combobox that has related records. But the deletion did not take
place. But internally, Jet may still be in transaction mode?????

[EManning]

On Jun 20, 8:49*am, Tom van Stiphout <[e-mail]:

On Fri, 20 Jun 2008 06:32:28 -0700 (PDT), EManning

<[e-mail]:

Are you doing a delete above this code block? *The error message seems
to indicate as much. There could be a transaction involved there.

Select queries never use transactions (implicit or not), or at least
they are never needed, and Access I'm sure won't use them.

-Tom.

On Jun 19, 10:10*pm, Tom van Stiphout <[e-mail]:

On Thu, 19 Jun 2008 07:18:15 -0700 (PDT), EManning

<[e-mail]:

The term "transaction" more than likely refers to a database
transaction. As in a stored procedure with:
BEGIN TRANS
--Do Something
COMMIT TRANS

or the transaction support in DAO or ADO.

Is that what you are doing?

-Tom.

Using A2003. *I'm receiving this error when returning from a
"DoCmd.OpenReport..." statement. *I have a tab control with a subform
on every tab. *The user selects an item from a combobox at the top of
the main form which refreshes all the subforms. *If this DoCmd
statement is not executed, then I don't receive the error. *I trieda
"DoCmd.OpenQuery..." instead of a report but get the same error.

I want the user to be able to print-preview a report if they try to
delete an item from any combobox on the subforms where there are
related records. *They're given an option to print-preview a report
showing the related records. *If they choose to print-preview, then
this error occurs on trying to move to another record. *If they choose
not to print-preview, then the error doesn't occur.

It appears to be something with the DoCmd property. *I'm finding some
posts on this newsgroup about getting this error after deleting
records and being careful about how subforms are linked. *If there are
no related records, then the item is deleted and I'm able to go on to
another record. *The subforms refresh just fine if the DoCmd is not
executed.

Any help or advice on how to fix or work-around this problem is
appreciated.- Hide quoted text -

- Show quoted text -

Thanks for your reply. *No, I'm not implicitly doing a transaction.
Here is the "offending" code...

Private Sub Form_Error(DataErr As Integer, Response As Integer)

* *Select Case DataErr
* * * *Case 3200, 3146
* * * * * *strTitle = "Cannot delete - there are projectsassigned
this department"
* * * * * *strMsg = "Would you like to print a list of these
projects?" & vbCrLf & vbCrLf & _
* * * * * * *"Click on Yes to print, No to cancel."

* * * * * *If MsgBox(strMsg, vbYesNo, strTitle) = vbYes Then
* * * * * * * *DoCmd.OpenReport "rpt_tlkpDepartments",
acViewPreview, , , , CInt(Me.DepartmentID)
* * * * * *End If

* * * * * *Response = acDataErrContinue
* * * *Case Else

The error does not occur if the user chooses not to print the report.
If they do choose to print, then the error occurs when they try to go
to another project.

According to Help, a transaction can occur like you stated or "or an
internal transaction created by the Microsoft Jet database engine,
such as when you delete multiple records in a single operation.".
This DoCmd is executed because the user tried to delete an item in a
combobox that has related records. *But the deletion did not take
place. *But internally, Jet may still be in transaction mode?????- Hide quoted text -

- Show quoted text -

Here's the coding that triggers error #3200:

Private Sub Form_Delete(Cancel As Integer)
On Error GoTo Form_Delete_Error

strTitle = "Are you sure you want to delete " & conQuote &
Me.Department & conQuote & "?"
strMsg = "Click on Yes to delete, No to cancel."
If MsgBox(strMsg, vbYesNo, strTitle) = vbNo Then
Cancel = True
End If

Form_Delete_Resume:
Exit Sub
<...snip...>
Then there'a a single line of code in the Form_BeforeDelConfirm event:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As
Integer)
On Error GoTo Form_BeforeDelConfirm_Error

Response = acDataErrContinue

Form_BeforeDelConfirm_Resume:
Exit Sub
<...snip...>

Thanks for your help...

[Bruce]

On Jun 20, 9:08 am, EManning <[e-mail]:

On Jun 20, 8:49 am, Tom van Stiphout <[e-mail]:

On Fri, 20 Jun 2008 06:32:28 -0700 (PDT), EManning

<[e-mail]:

Are you doing a delete above this code block? The error message seems
to indicate as much. There could be a transaction involved there.

Select queries never use transactions (implicit or not), or at least
they are never needed, and Access I'm sure won't use them.

-Tom.

>On Jun 19, 10:10 pm, Tom van Stiphout <[e-mail]:
>On Thu, 19 Jun 2008 07:18:15 -0700 (PDT), EManning

><[e-mail]:

>The term "transaction" more than likely refers to a database
>transaction. As in a stored procedure with:
>BEGIN TRANS
>--Do Something
>COMMIT TRANS

>or the transaction support in DAO or ADO.

>Is that what you are doing?

>-Tom.

>Using A2003. I'm receiving this error when returning from a
>"DoCmd.OpenReport..." statement. I have a tab control with a subform
>on every tab. The user selects an item from a combobox at the top of
>the main form which refreshes all the subforms. If this DoCmd
>statement is not executed, then I don't receive the error. I tried a
>"DoCmd.OpenQuery..." instead of a report but get the same error.

>I want the user to be able to print-preview a report if they try to
>delete an item from any combobox on the subforms where there are
>related records. They're given an option to print-preview a report
>showing the related records. If they choose to print-preview, then
>this error occurs on trying to move to another record. If they choose
>not to print-preview, then the error doesn't occur.

>It appears to be something with the DoCmd property. I'm finding some
>posts on this newsgroup about getting this error after deleting
>records and being careful about how subforms are linked. If there are
>no related records, then the item is deleted and I'm able to go on to
>another record. The subforms refresh just fine if the DoCmd is not
>executed.

>Any help or advice on how to fix or work-around this problem is
>appreciated.- Hide quoted text -

>- Show quoted text -

>Thanks for your reply. No, I'm not implicitly doing a transaction.
>Here is the "offending" code...

>Private Sub Form_Error(DataErr As Integer, Response As Integer)

Select Case DataErr
Case 3200, 3146
strTitle = "Cannot delete - there are projects assigned
>this department"
strMsg = "Would you like to print a list of these
>projects?" & vbCrLf & vbCrLf & _
"Click on Yes to print, No to cancel."

If MsgBox(strMsg, vbYesNo, strTitle) = vbYes Then
DoCmd.OpenReport "rpt_tlkpDepartments",
>acViewPreview, , , , CInt(Me.DepartmentID)
End If

Response = acDataErrContinue
Case Else

>The error does not occur if the user chooses not to print the report.
>If they do choose to print, then the error occurs when they try to go
>to another project.

>According to Help, a transaction can occur like you stated or "or an
>internal transaction created by the Microsoft Jet database engine,
>such as when you delete multiple records in a single operation.".
>This DoCmd is executed because the user tried to delete an item in a
>combobox that has related records. But the deletion did not take
>place. But internally, Jet may still be in transaction mode?????- Hide quoted text -

- Show quoted text -

Here's the coding that triggers error #3200:

Private Sub Form_Delete(Cancel As Integer)
On Error GoTo Form_Delete_Error

strTitle = "Are you sure you want to delete " & conQuote &
Me.Department & conQuote & "?"
strMsg = "Click on Yes to delete, No to cancel."
If MsgBox(strMsg, vbYesNo, strTitle) = vbNo Then
Cancel = True
End If

Form_Delete_Resume:
Exit Sub
<...snip...>

Then there'a a single line of code in the Form_BeforeDelConfirm event:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As
Integer)
On Error GoTo Form_BeforeDelConfirm_Error

Response = acDataErrContinue

Form_BeforeDelConfirm_Resume:
Exit Sub
<...snip...>

Thanks for your help...

Perhaps it would be easier to explicitly check for related records
prior to the deletion attempt rather than trap for the error that
occurs when the deletion fails. You could do this in the form's
Delete event using a dlookup() or your own custom function to
determine the existence of related records. If there are related
records, set Cancel = True in the Delete event of the form.

Bruce

[EManning]

On Jun 20, 5:26*pm, Bruce <[e-mail]:

On Jun 20, 9:08 am, EManning <[e-mail]:

On Jun 20, 8:49 am, Tom van Stiphout <[e-mail]:

On Fri, 20 Jun 2008 06:32:28 -0700 (PDT), EManning

<[e-mail]:

Are you doing a delete above this code block? *The error message seems
to indicate as much. There could be a transaction involved there.

Select queries never use transactions (implicit or not), or at least
they are never needed, and Access I'm sure won't use them.

-Tom.

On Jun 19, 10:10 pm, Tom van Stiphout <[e-mail]:
On Thu, 19 Jun 2008 07:18:15 -0700 (PDT), EManning

<[e-mail]:

The term "transaction" more than likely refers to a database
transaction. As in a stored procedure with:
BEGIN TRANS
--Do Something
COMMIT TRANS

or the transaction support in DAO or ADO.

Is that what you are doing?

-Tom.

Using A2003. *I'm receiving this error when returning from a
"DoCmd.OpenReport..." statement. *I have a tab control with a subform
on every tab. *The user selects an item from a combobox at the top of
the main form which refreshes all the subforms. *If this DoCmd
statement is not executed, then I don't receive the error. *I tried a
"DoCmd.OpenQuery..." instead of a report but get the same error.

I want the user to be able to print-preview a report if they try to
delete an item from any combobox on the subforms where there are
related records. *They're given an option to print-preview a report
showing the related records. *If they choose to print-preview, then
this error occurs on trying to move to another record. *If they choose
not to print-preview, then the error doesn't occur.

It appears to be something with the DoCmd property. *I'm findingsome
posts on this newsgroup about getting this error after deleting
records and being careful about how subforms are linked. *If there are
no related records, then the item is deleted and I'm able to go onto
another record. *The subforms refresh just fine if the DoCmd is not
executed.

Any help or advice on how to fix or work-around this problem is
appreciated.- Hide quoted text -

- Show quoted text -

Thanks for your reply. *No, I'm not implicitly doing a transaction.
Here is the "offending" code...

Private Sub Form_Error(DataErr As Integer, Response As Integer)

* *Select Case DataErr
* * * *Case 3200, 3146
* * * * * *strTitle = "Cannot delete - there are projects assigned
this department"
* * * * * *strMsg = "Would you like to print a list ofthese
projects?" & vbCrLf & vbCrLf & _
* * * * * * *"Click on Yes to print, No to cancel."

* * * * * *If MsgBox(strMsg, vbYesNo, strTitle) = vbYes Then
* * * * * * * *DoCmd.OpenReport "rpt_tlkpDepartments",
acViewPreview, , , , CInt(Me.DepartmentID)
* * * * * *End If

* * * * * *Response = acDataErrContinue
* * * *Case Else

The error does not occur if the user chooses not to print the report.
If they do choose to print, then the error occurs when they try to go
to another project.

According to Help, a transaction can occur like you stated or "or an
internal transaction created by the Microsoft Jet database engine,
such as when you delete multiple records in a single operation.".
This DoCmd is executed because the user tried to delete an item in a
combobox that has related records. *But the deletion did not take
place. *But internally, Jet may still be in transaction mode?????- Hide quoted text -

- Show quoted text -

Here's the coding that triggers error #3200:

Private Sub Form_Delete(Cancel As Integer)
On Error GoTo Form_Delete_Error

* * strTitle = "Are you sure you want to delete " & conQuote &
Me.Department & conQuote & "?"
* * strMsg = "Click on Yes to delete, No to cancel."
* * If MsgBox(strMsg, vbYesNo, strTitle) = vbNo Then
* * * * Cancel = True
* * End If

Form_Delete_Resume:
* * Exit Sub
<...snip...>

Then there'a a single line of code in the Form_BeforeDelConfirm event:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As
Integer)
On Error GoTo Form_BeforeDelConfirm_Error

* * Response = acDataErrContinue

Form_BeforeDelConfirm_Resume:
* * Exit Sub
<...snip...>

Thanks for your help...

Perhaps it would be easier to explicitly check for related records
prior to the deletion attempt rather than trap for the error that
occurs when the deletion fails. *You could do this in the form's
Delete event using a dlookup() or your own custom function to
determine the existence of related records. *If there are related
records, set Cancel = True in the Delete event of the form.

Bruce- Hide quoted text -

- Show quoted text -

Thanks for your reply. I tried this earlier but continued to get the
error. It's something with the DoCmd property in the Form_Delete
event.

[EManning]

On Jun 20, 5:26*pm, Bruce <[e-mail]:

On Jun 20, 9:08 am, EManning <[e-mail]:

On Jun 20, 8:49 am, Tom van Stiphout <[e-mail]:

On Fri, 20 Jun 2008 06:32:28 -0700 (PDT), EManning

<[e-mail]:

Are you doing a delete above this code block? *The error message seems
to indicate as much. There could be a transaction involved there.

Select queries never use transactions (implicit or not), or at least
they are never needed, and Access I'm sure won't use them.

-Tom.

On Jun 19, 10:10 pm, Tom van Stiphout <[e-mail]:
On Thu, 19 Jun 2008 07:18:15 -0700 (PDT), EManning

<[e-mail]:

The term "transaction" more than likely refers to a database
transaction. As in a stored procedure with:
BEGIN TRANS
--Do Something
COMMIT TRANS

or the transaction support in DAO or ADO.

Is that what you are doing?

-Tom.

Using A2003. *I'm receiving this error when returning from a
"DoCmd.OpenReport..." statement. *I have a tab control with a subform
on every tab. *The user selects an item from a combobox at the top of
the main form which refreshes all the subforms. *If this DoCmd
statement is not executed, then I don't receive the error. *I tried a
"DoCmd.OpenQuery..." instead of a report but get the same error.

I want the user to be able to print-preview a report if they try to
delete an item from any combobox on the subforms where there are
related records. *They're given an option to print-preview a report
showing the related records. *If they choose to print-preview, then
this error occurs on trying to move to another record. *If they choose
not to print-preview, then the error doesn't occur.

It appears to be something with the DoCmd property. *I'm findingsome
posts on this newsgroup about getting this error after deleting
records and being careful about how subforms are linked. *If there are
no related records, then the item is deleted and I'm able to go onto
another record. *The subforms refresh just fine if the DoCmd is not
executed.

Any help or advice on how to fix or work-around this problem is
appreciated.- Hide quoted text -

- Show quoted text -

Thanks for your reply. *No, I'm not implicitly doing a transaction.
Here is the "offending" code...

Private Sub Form_Error(DataErr As Integer, Response As Integer)

* *Select Case DataErr
* * * *Case 3200, 3146
* * * * * *strTitle = "Cannot delete - there are projects assigned
this department"
* * * * * *strMsg = "Would you like to print a list ofthese
projects?" & vbCrLf & vbCrLf & _
* * * * * * *"Click on Yes to print, No to cancel."

* * * * * *If MsgBox(strMsg, vbYesNo, strTitle) = vbYes Then
* * * * * * * *DoCmd.OpenReport "rpt_tlkpDepartments",
acViewPreview, , , , CInt(Me.DepartmentID)
* * * * * *End If

* * * * * *Response = acDataErrContinue
* * * *Case Else

The error does not occur if the user chooses not to print the report.
If they do choose to print, then the error occurs when they try to go
to another project.

According to Help, a transaction can occur like you stated or "or an
internal transaction created by the Microsoft Jet database engine,
such as when you delete multiple records in a single operation.".
This DoCmd is executed because the user tried to delete an item in a
combobox that has related records. *But the deletion did not take
place. *But internally, Jet may still be in transaction mode?????- Hide quoted text -

- Show quoted text -

Here's the coding that triggers error #3200:

Private Sub Form_Delete(Cancel As Integer)
On Error GoTo Form_Delete_Error

* * strTitle = "Are you sure you want to delete " & conQuote &
Me.Department & conQuote & "?"
* * strMsg = "Click on Yes to delete, No to cancel."
* * If MsgBox(strMsg, vbYesNo, strTitle) = vbNo Then
* * * * Cancel = True
* * End If

Form_Delete_Resume:
* * Exit Sub
<...snip...>

Then there'a a single line of code in the Form_BeforeDelConfirm event:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As
Integer)
On Error GoTo Form_BeforeDelConfirm_Error

* * Response = acDataErrContinue

Form_BeforeDelConfirm_Resume:
* * Exit Sub
<...snip...>

Thanks for your help...

Perhaps it would be easier to explicitly check for related records
prior to the deletion attempt rather than trap for the error that
occurs when the deletion fails. *You could do this in the form's
Delete event using a dlookup() or your own custom function to
determine the existence of related records. *If there are related
records, set Cancel = True in the Delete event of the form.

Bruce- Hide quoted text -

- Show quoted text -

I ended up writing my own deletion process. There's just something
about subforms, DoCmd, and the Form_Delete event not playing nicely
together.