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
Code:
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
Code:
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
Code:
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.