Warning to user if they attempt to change data on a form

Posted on 2014-08-06
Last Modified: 2014-08-06
I'm trying to provide a warning message to a user if they attempt to change data on an existing record via a form event.

Here is my code which give them the warning but accepts the change even if they reply "No".  What am I doing wrong?

Private Sub Form_DataChange(ByVal Reason As Long)

        If MsgBox("You are attempting to change data in an existing record. Do you wish to continue?  If so, click [Yes].  If not, click [No]", vbYesNo) = vbNo Then
        Exit Sub
        'Do nothing
        End If

End Sub

Open in new window

Question by:SteveL13

    Author Comment

    By the way, if the record is a new record I don't want the warning to appear.
    LVL 56

    Accepted Solution

    The data change event is only when Pivot tables are in use.

     Use the OnDirty event.

    LVL 33

    Assisted Solution

    I concur, the Dirty event is appropriate for this requirement.  To take the explanation further.  Once you have asked the question, you need to cancel the update if they say to.
    Private Sub Form_Dirty(Cancel As Integer)
        If Me.NewRecord  = True Then
            Exit Sub
            If Msgbox("Are you sure you want to update this record?", vbYesNo) = vbNo Then
                Cancel = True
                Exit Sub
            End If
        End If
    End Sub

    Open in new window

    Put your validation code in the FORM's BeforeUpdate event and then Cancel that event if you find an error but don't use Me.Undo since in that case, you don't want to remove all the changes the user made.  I also use the .SetFocus property to set focus to the control with the error and then exit immediately.  Using this technique, if they have three errors, it takes three submits to find them all but unless you create some fancy reporting mechanism, it's just simpler to report them one at a time.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now