Deleting record from bound form

[gershwyn]

In Access 2007, I am trying to add a button to my sata entry split form that will delete the current record, after prompting, but I am running into a bizarre issue.

Here is the relevant code. Pressing the button triggers the btnDelete_Click() subroutine, which calls DeleteRecord() which in turn can call MoveLine().

Private Sub btnDelete_Click()
  L = txtLine.Value
  If Form.NewRecord Then
    'The current record has never been saved, so simply clear the form
    Form.Undo
    txtItem.SetFocus
  Else
    'Prompt user with confirmation dialog
    rVal = MsgBox("Are you sure you want to delete this record?", vbYesNo)
    If rVal = vbNo Then Exit Sub
 
    'Delete the current record and update datasheet
    R = Form.CurrentRecord
    DeleteRecord DetailID
    Me.Requery
 
    'Go to the record after the one we just deleted, or go to the end if we deleted the last record
    If R > Form.Recordset.RecordCount Then
      DoCmd.GoToRecord acActiveDataObject, , acLast
    Else
      DoCmd.GoToRecord acActiveDataObject, , acGoTo, R
    End If
  End If
End Sub
 
Private Sub DeleteRecord(ID)
  'First, count the number of records in the order
  Ord = cmbOrder.Value
  Cnt = DCount(1, "OrderDetail", "OrderNumber = " & Ord)
 
  'Delete the specified record
  DoCmd.SetWarnings False
  Line = DLookup("LineID", "OrderDetail", "DetailID = " & ID)
 
  RunCommand acCmdDeleteRecord
  DoCmd.SetWarnings True
 
  'If the record we just deleted was not the last record, shift any records after it down by one
  If Line < Cnt Then MoveLine Line, Cnt, Ord
End Sub
 
Private Sub MoveLine(Start, Finish, Ord)
  'Don't do anything if start and finish are the same, or if there are no records to move
  If Finish < 1 Then Finish = 1
  If Start = Finish Then Exit Sub
  Cnt = DMax("LineID", "OrderDetail", "OrderNumber = " & Ord)
  If Cnt < 1 Then Exit Sub
  If Finish > Cnt Then Finish = Cnt
 
  'If we're moving a record down, adjust the intervening records up and vice versa
  If Start > Finish Then Adj = "+ 1" Else Adj = "- 1"
 
  'Renumber the affected lines
  DoCmd.SetWarnings False
  Query = "UPDATE [OrderDetail] SET [LineID] = [LineID] " & Adj & ", [RenumberLine] = [LineID] " & Adj & " WHERE [LineID] BETWEEN " & _
          Start & " AND " & Finish & " AND [OrderNumber] = " & Ord & ";"
  DoCmd.RunSQL Query, False
  DoCmd.SetWarnings True
End Sub

The issue is this: deleting the item itself works fine, so long as they don't then try to add another item before closing the form. If they delete and item and then add a new item, a really bizarre thing happens. The line appears twice in the split form portion, though the second one is unselectable. Selecting or scrolling the records near the phantom record causes them to sometimes display the incorrect information for that line. There are not two records in the underlying table, and closing the form and reopening it makes everything fine again.

I added the requery line specifically to try to get the form back in sync with the table, but it doesn't seem to help. Any suggestions as to how best to resolve this are greatly appreciated.