Access 2007 Form Error: You tried to commit or Rollback a Transaction

I have a really frustrating problem and I can't find a workaround or solution anywhere. Unfortunately it is quite difficult to describe (apologies for the length) but here goes:

I have a continuous form (let's call it the 'view' form) that displays transactions for a bank account. It is a fairly simple form that just displays rows from a single bound table. It is possible to delete transactions via dbl. click on the record selector. After confirmation, this deletes the record form the table and then recalculates the running balance. It does some jiggery-pokery to keep the recalculation as efficient as possible and it works just fine 99% of the time. New records can be added via a separate modal form invoked from an 'Add' button on the form header. The 'add' form is closed after a record is saved. After a record is added the view form is re-queried and the running balances re-caclulated. This also works fine.

Now to the problem.

If I add a record, irrespective of where it appears in the view form (keyed on date) and the immediately delete it, I get a form error (not a normal trappable runtime error) as follows:

"You tried to commit or Rollback a Transaction without first beginning a transaction."

As it happens, I do use a transaction within the balance recalculation routine (which under every other situation works perfectly):

            strSQL = "Select * from ACTransaction where ACT_BankAccountID = " & Me.ComboBankAccount & " order by ACT_TaxDate, ACT_ID;"
        Set rsTrans = CurrentDb.OpenRecordset(strSQL)
        wFindString = "ACT_TaxDate >= " & wUSFormatDate

        rsTrans.FindFirst wFindString
        wStartRecPosition = rsTrans.AbsolutePosition
        If wStartRecPosition > 0 Then
            rsTrans.MovePrevious
            wrunningbalance = rsTrans("ACT_TxtBalance")
        Else
            wrunningbalance = Me.TxtOpenBal
        End If
        If wStartRecPosition > 0 Then
            rsTrans.MoveNext
        End If

        On Error GoTo 0
        On Error GoTo DoRollback

        DBEngine(0).BeginTrans

        If Not (rsTrans.EOF And rsTrans.BOF) Then
            Do Until rsTrans.EOF = True

                wTrans = rsTrans("ACT_ID")

                wTxValue = rsTrans("ACT_ValTTC") * rsTrans("ACT_TxtTC_Sign")
                wrunningbalance = wrunningbalance + wTxValue

                rsTrans.Edit
                    rsTrans("ACT_TxtBalance") = wrunningbalance
                rsTrans.Update

            rsTrans.MoveNext
            Loop
        End If

        DBEngine(0).CommitTrans

        rsTrans.Close
        Set rsTrans = Nothing

Now, it would seem obvious that there is something wrong with my error handling / rollback etc. but an error is not firing. If I completely remove the transaction handling it still fails with the same error.

I've riddled my code with debug to follow the course of events and the error message is being presented before it even gets to the balance recalc code. The last event triggered is the before_DelConfirm (where I have a "DoCmd.SetWarnings False") event. After the above error is presented it tells me:

"The changes you've made can't be saved" "The save operation may have failed due to the temporary locking of the records by another user"

If I 'Esc' through the error messages then processing proceeds to the after_delConfirm (where I have a "DoCmd.SetWarnings True") and then into the balance recalculation procedure whereupon it works perfectly.

Given that it works OK apart from the errors (though there's obviously something wrong) I've even tried putting:

Response = acDataErrContinue

into the Form_Error event where I also debugged the 'DataErr' value: It alternates endlessly between 3034 and 2111.

It must be something to do with record buffering and/or Transactions (I've not used Access Transactions before) as if I close the view form and re-open it then I can delete the offending 'new' transaction without any problem. I've tried sticking a doEvents after the modal 'add' form closes and also in the Form_Delete but that makes no difference.

Any help from you clever people out there would be very much appreciated; I'm completely flummoxed.

EDIT : I've tried putting a "dbForceOSFlush" on the Commit but it makes no difference.

Solution

Found it while ploughing through every line of code in both the add and view forms. Much as expected, it was nothing to do with the transaction in the balance recalculation routine.

In the 'view' form I have a routing that moves the current row position to the last few rows via a simple recordset bookmarking operation. As a matter of course, whenever I code a "set rs = currentdb.openrecordset(..)" I immediately code the "rs.close" and "set rs = nothing". In this instance, for whatever reason, I did not so there was a stray object floating around confusing things. Adding those two little lines of code did the trick.

Thanks to everybody who took the time to look at this for me. It's amazing how often sleeping on a problem and a bit of last-ditch persistence lead to the solution.