none
Requery gets "Operation not supported in transaction" in form event RRS feed

  • Question

  • (Access 2016 on Win10 Pro x64)

    I have a form, datasheet, that shows records with field values from a joined query. Since a "Delete" only wants to delete from one of the tables of the underlying query, I added an OnDelete macro that does a RunSql DELETE query for the correct table. and then does a CancelEvent. All that works. (The table to delete from is just a many-to-many junction table with two foreign keys. In the query I show fields from the two joined tables so the join can be understood instead of just showing two meaningless autonumber key values.)

    To restore sanity to the form, I added a Requery as the last action in the query. Now I get "Operation not supported in transaction". (With or without the RunSQL specifying a Transaction.) After dismissing the error, a simple F5 gets the form contents just the way they should be.

    Why can't I have a Requery after a RunSQL? Is there a workaround?

    Thursday, August 11, 2016 7:26 AM

Answers

  • Hi Dick Watson,

    According to your description, I suggest that you could Form.AfterDelConfirm event that the AfterDelConfirm event occurs after the user confirms the deletions and the records are actually deleted or when the deletions are canceled.

    For more information, click here to refer about Form.AfterDelConfirm Event (Access)

    • Proposed as answer by David_JunFeng Monday, August 22, 2016 9:32 AM
    • Marked as answer by David_JunFeng Tuesday, August 23, 2016 1:17 AM
    Wednesday, August 17, 2016 7:17 AM

All replies

  • >>>Why can't I have a Requery after a RunSQL? Is there a workaround?

    According to your description, there is less information to let us reproduce your issue, so I suggest that you could provide more details, for example table structure, query and screenshot etc., that will help us reproduce and resolve it.

    In addition you could refer to ACC2002: Error Message: Operation Not Supported in Transactions

    Thanks for your understanding.
    Friday, August 12, 2016 6:44 AM
  • I think that one just cannot do the Requery in the Delete event. The way I work around this is to set the AllowDeletes property to No and place an extra "Delete" command button on the form from where I run the delete query and the requery (plus eventually a repositioning of the current record because the requery sets the first record as current).

    Matthias Kläy, Kläy Computing AG

    Friday, August 12, 2016 12:31 PM
  • >>>Why can't I have a Requery after a RunSQL? Is there a workaround?

    According to your description, there is less information to let us reproduce your issue, so I suggest that you could provide more details, for example table structure, query and screenshot etc., that will help us reproduce and resolve it.

    In addition you could refer to ACC2002: Error Message: Operation Not Supported in Transactions

    Thanks for your understanding.

    Reproducing should be pretty simple: create a Delete, or even, apparently, Before Delete Confirm, event macro with the Requery action. Seems like RunSQL has nothing to do with it. CancelEvent doesn't either. Requery in the Delete event or the Before Delete Confirm event just doesn't work.

    Saturday, August 13, 2016 12:36 AM
  • I think that one just cannot do the Requery in the Delete event.

    That seems about right. Doesn't work in Before Delete Confirm event, either.

    Every time I work with Access forms, I get reminded why it's been a long time since I last did.

    Saturday, August 13, 2016 12:38 AM
  • Hi Dick Watson,

    According to your description, could you resolve your issue when you follow mklaey suggestion? If not, you could upload your Access database file on OneDrive, then proive this link, we will download and try to reproduce and resolve this issue.

    Thanks for your understanding.
    Monday, August 15, 2016 8:47 AM
  • Yes, I have re-factored/redesigned my application to workaround this problem with Access. No, the problem with Access hasn't gone away.

    Testcase created and available at https://1drv.ms/u/s!AkmpnS23GE8YjaBBxH8isaYgRT77mQ.

    One table with one extra field.
    One record.
    One form with two controls and one macro with one action.

    Open the form. Select the record from the record selector on the side. Hit Del. You will get the "Operation not supported in transaction" error.

    Monday, August 15, 2016 5:52 PM
  • Hi Dick Watson,

    Thanks very much for your detail steps to help us reproduce this issue. I’m able to reproduce this issue now. This issue is caused by that you did not create Requery Macro Action correctly, so I suggest that you could modify like below:

    For more information, click here to refer about Requery Macro Action

    Tuesday, August 16, 2016 8:24 AM
  • That's like saying "it's wrong to want to drive to the airport since you won't get in a collision walking to the park." Requerying the control does nothing unless the control has a record source of its own. As the link your provided points out, "Leave this argument blank to requery the source of the active object. If the active object is a datasheet or a query result set, you must leave this argument blank."
    Tuesday, August 16, 2016 5:33 PM
  • Hi Dick Watson,

    According to your description, I suggest that you could Form.AfterDelConfirm event that the AfterDelConfirm event occurs after the user confirms the deletions and the records are actually deleted or when the deletions are canceled.

    For more information, click here to refer about Form.AfterDelConfirm Event (Access)

    • Proposed as answer by David_JunFeng Monday, August 22, 2016 9:32 AM
    • Marked as answer by David_JunFeng Tuesday, August 23, 2016 1:17 AM
    Wednesday, August 17, 2016 7:17 AM