Tuesday, 27 October 2015

Creating a “save record?” prompt

By default, any changes made to a bound record in a form are automatically saved whenever you move away from that record - this could be by moving to another record, by creating a new record or by closing the form. This is generally fine, but what if an unplanned change has been made in error? Wouldn't it be nice to get a “record changed - save changes?” type prompt when you move away from a record?

Fortunately this is easily achieved through use of the form's BeforeUpdate event - this is triggered by saving changes to a bound record (that's why it called “before update”, after all), so it's triggered when you move away from a modified record. Of course, it's also triggered if you do an explicit Save operation, but that's okay, it then just gives you a chance to confirm that you want to save, which is generally a good thing too. Anyway, here's the code.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Select Case MsgBox("Do you want to save changes to this record?", vbYesNoCancel + vbQuestion)
    Case vbYes 'Go-ahead and save
        'Don't have to do anything
    Case vbNo 'Undo all changes, close without saving
        Me.Undo
    Case Else 'Cancel change, continue editing
        Cancel = True
    End Select
End Sub

No comments:

Post a comment