Message Box on Record Change

Question

I have a form that queries records that the user may want to edit. I want the user to only be able to save the record if they've clicked the 'Save' button. Hitting the 'Close' button will prompt the user if they haven't saved yet, and may ask if they want to save.

I'm encountering a problem when the user changes through the records: I want to have a Y/N message box prompt the user for saving the changes they made to the previous record, otherwise their changes will be discarded. I have the following code set up:

Private Sub CmdCloseForm_Click()

If Me.Dirty Then
    'checks that needed fields are completed
    If IsFormValidated = False Then
        If MsgBox("Required fields aren't filled." & vbCrLf & "Would you like to close this form without saving?", vbYesNo + vbQuestion + vbDefaultButton2, "Warning") = vbNo Then
            Exit Sub
        End If
    Else
        'checks if form has been saved already
        If mSaved = False Then
            Select Case MsgBox("Form hasn't been saved. Do you want to save and close?" & vbCrLf & "If you click 'No' the form will close without saving.", vbQuestion + vbYesNoCancel, "Save As")
                'selecting yes will save and close form
                Case vbYes:
                    mSaved = True
                'selecting no will close the form w/o saving
                Case vbNo:
                    mSaved = False
                'selecting cancel will cancel out of the prompt
                Case vbCancel:
                    Exit Sub
            End Select
        ElseIf mSaved = True Then
            'if form has been previously saved, will finally close the form
            If MsgBox("Would you like to close this form?", vbYesNo + vbQuestion + vbDefaultButton2, "Close Form") = vbNo Then
                Exit Sub
            End If
        End If
    End If
End If

DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

'won't save automatically unless mSaved is true
Private Sub Form_BeforeUpdate(Cancel As Integer)
'if mSaved = False then the record won't save
If mSaved = False Then
    Cancel = True
    Me.Undo
    Cancel = False
End If
End Sub

I pretty much want the 'CmdCloseForm' Message Boxes to run when the user moves on to the next record. Is there any way to do this?

Sergey S. Sergey S. · Accepted Answer · 2017-05-31T08:38:15

If you really want to ask user about saving changes for each row, ask in Form_BeforeUpdate and in Form_BeforeDelConfirm. Those events will be fired each time when user changes edited record, or when subform with edited records loses focus, or user closes form with data. But this is not good solution because messageboxes will be too annoying. The better way is to copy edited data to temporary table, allow user edit the data and copy back to source table changed data when user clicks "Save". It's quite simple if you don't need multi-user data editing, in this case you will need some additional code for avoiding collisions.

Message Box on Record Change

1 Answers