Ivalid object or object no longer set

[Salty Mariner]

I have the following code in a continuous forms subform which ensures that my sort field is sequentially numbered. I run it from the forms OnLoad event and it works exactly as expected.

Public Sub SetInitialSort(SortField As String, frm As Access.Form)


    ' Ensure the form is not dirty
    If frm.Dirty Then
        frm.Dirty = False
    End If


    On Error GoTo ErrorHandler
    Dim rs As DAO.Recordset


    Set rs = frm.RecordsetClone


    If Not (rs Is Nothing) Then
        If Not (rs.BOF And rs.EOF) Then
            rs.MoveFirst


            Do While Not rs.EOF
                If rs.EditMode = dbEditNone Then rs.Edit
                rs.Fields(SortField) = rs.AbsolutePosition + 1
                rs.Update
                rs.MoveNext
            Loop


            rs.MoveFirst
        End If
    Else
        MsgBox "RecordsetClone is not set."
    End If


    Set rs = Nothing ' Clean up
    frm.Requery ' Refresh the form's recordset
    Exit Sub


ErrorHandler:
    MsgBox "Error Number: " & Err.Number & "; Description: " & Err.Description
End Sub

If I try to run this after deleting a record it fails and I get the error "Invalid Object or object no longer set" I have tried trapping errors to see if it is no longer set and as far as I can tell the object rs is still set. that leads to my question about why it would become an invalid object?

I have found a workaround that works though probably not ideal by running the following code basically turning off screen updating, closing and re-opening. It all appears to work pretty seamlessly but is probably less than ideal. Any ideas on why a DAO recordset would work fine when loading but not later?

Public Sub ReopenForm(formName As String)
    Application.Echo False ' Turn off screen updating
    DoCmd.Close acForm, formName, acSaveNo ' Close the form without saving
    DoCmd.OpenForm formName ' Reopen the form
    Application.Echo True ' Turn on screen updating
End Sub

[June7]

When and how does record deletion occur? Why would recordset be open when this deletion occurs? How do you run again?

I tested code and do not get error after deleting record. But guess I better test with subform now. Show code that calls the procedure from Load event.

[Salty Mariner]

When and how does record deletion occur? Why would recordset be open when this deletion occurs? How do you run again?

I tested code and do not get error after deleting record. But guess I better test with subform now. Show code that calls the procedure from Load event.

Here is the code that runs on Load

Private Sub Form_Load()
    If Forms!frmVoyagePlan!subVoyPlanLegs.Enabled = True Then
        Call SetInitialSort("LegNo", Me)
    End If
End Sub

This is the code I was trying to run to delete

Private Sub cmdDelete_Click()
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    Me.Requery
    Call SetInitialSort("LegNo", Me)
End Sub

It was after running this code and calling SetInitialSort that the error gets generated. Not having much luck at being able to solve the error I came up with the code to close and reopen which although it works seems like a kludge to me. I'd rather just have the code work as intended.

[June7]

Original code works for me. No Reopen. No error.

[davegri]

Don't know about OP's described problem but here's a potential other problem.

Existing code:

            Do While Not rs.EOF
                If rs.EditMode = dbEditNone Then rs.Edit
                rs.Fields(SortField) = rs.AbsolutePosition + 1
                rs.Update
                rs.MoveNext
            Loop

The above code will error if the IF is false. It will try to .update without a prior .edit
I would change it to this:

		Do While Not rs.EOF
                If rs.EditMode = dbEditNone Then 
		    rs.Edit
		    rs.Fields(SortField) = rs.AbsolutePosition + 1
		    rs.Update
		endif	
                rs.MoveNext
            Loop

[Salty Mariner]

Original code works for me. No Reopen. No error.

Strange...Something in my form seems to be preventing the rs variable from being set. when I single step through the code in the VBE as soon as I pass the Set statement any instance of the variable shows that error.....when the form is loaded. When stepping through code before the form loads it works as it should.

I added a command button to call and run the code. It fails after running delete record, but it will run just fine if I have not deleted anything. Could something in my delete code be causing the subform to become unbound? I'm just taking wild guesses at this point because it is the action of deleting a record that seems to be messing with the ability to use the variable it's almost as if the forms recordset suddenly disappeared.

[June7]

The recordset shouldn't even be open when delete takes place. It opens when procedure is called. Again, I have no issue with your code. Record deletes and SetInitialSort procedure is called.

[Salty Mariner]

OK, I solved this after spending an hour or so requesting code examples from MS CoPilot Ai Search. Here is what I came up with that works. I think it had something to do with the nested custom functions in my record source, although I can't be sure. I am now using the following delete code using SQL against the table

Public Sub DeleteSelectedRecord(RecordID As Long)
    Dim db As DAO.Database
    Dim sql As String
    
    ' Get the current database
    Set db = CurrentDb
    
    ' SQL statement to delete the selected record from tblVpLegs
    sql = "DELETE FROM tblVpLegs WHERE VLeg_ID = " & RecordID
    
    ' Execute the SQL statement
    db.Execute sql, dbFailOnError
    
    ' Optional: Handle errors and transaction commit/rollback if necessary
    
    ' Clean up
    Set db = Nothing
End Sub

This is called from a command button

Private Sub cmdDelete_Click()
    If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Confirm Delete") = vbYes Then
        ' Perform the SQL delete operation
        Call DeleteSelectedRecord(Me.VLeg_ID)
        
        Me.Requery
        
        ' Run the SetInitialSort function to sort the records
        Call ResetSort(Me, Me.VP_ID)
        
    End If
End Sub

That runs the following code to reset the sort order

Public Sub ResetSort(frm As Access.Form, intVpID As Long)
    Dim wrkCurrent As DAO.Workspace
    Dim dbs As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql As String
    Dim counter As Long


    ' Create a new Workspace object and use the default workspace
    Set wrkCurrent = DBEngine.Workspaces(0)
    Set dbs = CurrentDb


    ' Start the transaction
    wrkCurrent.BeginTrans


    ' Open a recordset on tblVpLegs for the specific VP_ID ordered by LegNo
    Set rs = dbs.OpenRecordset("SELECT VLeg_ID FROM tblVpLegs WHERE VP_ID = " & intVpID & " ORDER BY LegNo", dbOpenDynaset)


    ' Initialize the counter
    counter = 1


    ' Loop through the recordset and update the LegNo field
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        While Not rs.EOF
            ' SQL statement to update the LegNo field for the specific VP_ID
            sql = "UPDATE tblVpLegs SET LegNo = " & counter & " WHERE VLeg_ID = " & rs!VLeg_ID & " AND VP_ID = " & intVpID
            dbs.Execute sql, dbFailOnError
            
            ' Increment the counter
            counter = counter + 1
            
            ' Move to the next record
            rs.MoveNext
        Wend
    End If


    ' Commit the transaction
    wrkCurrent.CommitTrans


    ' Clean up
    rs.Close
    Set rs = Nothing
    Set dbs = Nothing
    wrkCurrent.Close
    Set wrkCurrent = Nothing


    ' Refresh the form to update the display
    frm.Requery
End Sub

I kept asking CoPilot questions and got a lot of half answers but as I learned to ask better questions it learned how to give me better responses. Using AI comes with a lot of challenges but eventually with enough requests I arrived at something that worked as intended. The numbering was kind of wonky in the initial code until we went with DAO workspaces and transactions. My big takeaway about transactions and workspaces is that they somehow maintain data integrity when doing things like running a loop. How exactly they do that I will have to learn more about as this is all new to me. Good news is that this all works now.