Delete form record after successful transaction fails at second attempt (1 Viewer)

[Daniela Z.]

Hi there!

Plan: Stuff is written for user review into a temporary table. The table is tied to a form which allows the user to conveniently review the records. Upon button press information contained in the current record gets written to other tables and after successful operation, the current entry in the form is deleted and the next record becomes the current record to review.

Implementation:

Private Sub CommitRecord_Click()
          ' Hook for rollback via transaction
10    On Error GoTo RollbackAdjustments

          ' Variable Declartion
          Dim strBookmark As String ' recordset bookmark
          
          Dim rsForm As DAO.Recordset
          Dim rsSubForm As DAO.Recordset
          
          Dim rsOther As DAO.Recordset
          
          Dim db As DAO.Database
          Dim wks As Workspace

20        Set rsForm = Me.RecordsetClone
30        Set rsSubForm = Me!sfrm.Form.RecordsetClone

          ' Start Transaction
40    Set wks = DBEngine(0)
50    wks.BeginTrans
60      Set db = wks.Databases(0)
          
        ' Recordsets init
70      Set rsOther = db.OpenRecordset("tbl", dbOpenTable)
                                          
        '''
        ' Commit user changes to tables
        ''
            ' Sync record in recordset clone 'rsForm' with current record on form
            ' (apparently there is no sync between clone and form when using form record selectors)
110         rsForm.Bookmark = Me.Bookmark
        
        ' Commit changes here ...
        
          ' Transaction sucessful, commit
910    wks.CommitTrans dbForceOSFlush
920    On Error GoTo ErrorHandling
          
       ' Deletion of processed entry from form recordset and form recordsetclone
        
        ' movenext on the recordsetclone to save the next record's position
930     rsForm.MoveNext
          
940     If Not rsForm.EOF Then                 ' if not end-of-file
950         strBookmark = rsForm.Bookmark      ' ...save the next record's bookmark
960         rsForm.Bookmark = Me.Bookmark      ' ...go back to the record to delete
970         rsForm.Delete                      ' ...delete the record
980         Me.Requery
990         Me.Bookmark = strBookmark          ' ...and return to the saved bookmark
1000   Else
1010        rsForm.Delete                      ' ...just delete the record
1020        Me.Requery
1030        rsForm.MoveLast                    ' ...move to the last record
1040        Me.Bookmark = rsForm.Bookmark      ' ...position the form to it
1050   End If
          
1060      Debug.Print "Success!"
          
ExitProcessing:
          ' Recordset cleanup
1070      rsForm.Close
1080      Set rsForm = Nothing
1090      rsSubForm.Close
1100      Set rsSubForm = Nothing
1110      rsOtherg.Close
1120      Set rsOther = Nothing
          
          ' Transaction & Db cleanup
1190      db.Close
1200      Set db = Nothing
1210      wks.Close
1220      Set wks = Nothing
          ' Exit
          
1240      Exit Sub
          
RollbackAdjustments:
1250      wks.Rollback
ErrorHandling:
1260      Debug.Print "Error Line: " & Erl
1270      Debug.Print "Error: (" & Err.Number & ") " & Err.Description
1280      If Err.Number <> 0 Then
1290        Err.Clear
1300      End If
1310      GoTo ExitProcessing
End Sub

Problem: The routine runs well on the first run-through, however, subsequent attempts to run and commit the user reviewed changes on the current record fail with `Error Line: 110, Error: (3420) Object invalid or no longer set.` especially if other records have been deleted from the form's recordset.

I would value some insight on the current problem, especially how to properly delete a form entry from both recordset and recordsetclone without the two getting out of sync. In addition, I would highly appreciate a reference to an authoritative doc that explains the operations on recordsetclones in depth – Andrew Couch's VBA Programming: Inside Out has been a good companion so far, but lacks detailed description of inner workings.

Many thanks in advance to anyone for giving this a read and possible advice!

 

[plog]

Stuff is written for user review into a temporary table.

So "temporary table" is the first red flag that put me on alert. And within 2 sentences there's the other:

Upon button press information contained in the current record gets written to other tables

The rest of your post sounds like you've over engineered a way to overcome your poor table structuring. Why not just have that "temporary table" be a permanent table. And instead of copying data out of it, simply mark it?

Let's use a library as a simple example. You've got a table of books. Books get loaned out, they get put on hold, they get lost. In a database you don't have a LoanedOut table where you copy data into, you don't have a OnHold table where you copy data into, you don't have a LostBooks table where you copy data into. You have a status field in your Books table and that field tells you what the status of every book is.

Why can't you do something similar? What is your database for exactly?

 

[Daniela Z.]

Thank you, plog, for weighing in. Reason for the temporary table is that the user has to manipulate some data and then decide on whether to make changes permanent or not. I had envisaged writing directly to the target tables and wrapping everything into a transaction, however, this interfered with the user interacting with the data, hence a temporary table which can easily be bound to a form.

The database is for storing contracts to evaluate the necessity/keep track of their modifications.

 

[Daniela Z.]

Form some reason the form's RecordsetClone becomes corrupt. I replaced line 100 where the error occurs with rsForm.FindFirst "lntID = " & Me.ID_ctl.Value and also moved out lines 1070 and 1090 where the closing of rsForm and rsSubform occurred, following recommendations in a post by LPurvis (titled ‹RecordsetClone Problems› on this forum. For some reason I the link is identified as spam) to no avail, this error 'Error Line: 110, Error: (3420) Object invalid or no longer set.' mystifies me, I would really like to understand what is going on.

 

[Gasman]

I would start walking the code with F8 and perhaps some breakpoints.?

You need a certain number of posts before you can post any links. This is to hinder spammers.

 

[ebs17]

I don't understand the whole process, it could probably be solved completely differently. Doesn't matter.

General information about the code: If I see 10 recordsets when I declare them, I get dizzy. This seems like nested individual processing in loops; I myself prefer queries and mass data processing.

rsOther...what is this for? It temporarily becomes rsOtherg, but is never actually used in either case.

If you get lost in the depths of your forms and their events as well as in the nested recordsets: Orient yourself on the basics - tables and queries. A record is in a table. With a given ID I can identify it immediately and thus delete it in the table or display it in a bound form. Flat logic structure, simple logic, clear and less prone to errors.
First think, then act.

 

[Minty]

I think it's because you need to reload the RecordsetClone after the update, as it will no longer be valid?

 

[Daniela Z.]

The problem is linked to the closure of the default workspace in line 1210 upon which the form's RecordsetClone somehow depends – if any one knows how and why, please let me know. What is the proper way of running a transaction on the current database in a separate workspace? Creating a new workspace ok, but then how do I reference the current database? Application.DefaultWorkspaceClone could be an option, but it seems outdated. I have come across many examples where the default workspace is closed without any issue.

 

[Daniela Z.]

I think it's because you need to reload the RecordsetClone after the update, as it will no longer be valid?

Hello Minty – what do you mean by reload?

 

[Gasman]

Hello Minty – what do you mean by reload?

I would guess at Set it again?

 

[Daniela Z.]

Well, I had already tried that before reaching the conclusion that the closure of the default workspace was the culprit:

The problem is linked to the closure of the default workspace in line 1210 upon which the form's RecordsetClone somehow depends – if any one knows how and why, please let me know. What is the proper way of running a transaction on the current database in a separate workspace? Creating a new workspace ok, but then how do I reference the current database? Application.DefaultWorkspaceClone could be an option, but it seems outdated. I have come across many examples where the default workspace is closed without any issue.