Tuesday 13 October 2015

Deleting a record tidily

When you delete a record from a form, by default the next available record is displayed when the deletion is complete. No problems there, you might think, but what if the record you're deleting is the last in the recordset? A blank record is displayed. Some might argue that this is just an untidy annoyance that can be lived with, but I'd rather not. Also, if you're using Me.NewRecord in the form's Current event to do something in the event of a new record, you'll find that "something" happening, which isn't very convenient! The following example shows the module for a form that moves you back to the previous record if you delete the last record in the form's recordset:

Option Compare Database
Option Explicit
Dim booDel As Boolean

Private Sub Form_Current()
    If booDel Then
        booDel=not booDel
        DoCmd.GoToRecord , , acPrevious
    End If
End Sub

Private Sub Form_Delete(Cancel As Integer)
    booDel=(Me.CurrentRecord=Me.RecordsetClone.RecordCount)
End Sub

No comments:

Post a Comment