Require use of Undo or Commit button

Posted on 2006-03-21
Last Modified: 2006-11-18
Each line item in my subform has a Commit Button, and an Undo Button.
Once the user starts making changes, I want to force them to use one of those two buttones.

If they accidientally try to leave the subform by clicking at a higher level, or by closing the main form, or even by closing access, I want to display a message:

"you must either commit or undo your change, before proceeding".  

But, they should be able to freely enter and leave the subform for nonupdate purposes  (like copying text to the clipboard)

-------------- here is the background -----------

My form is structured as follows

Level 1 frmOrderHdr   < allows user to select the Order Number
    Level 2 sfrmOrderDetail has these controls:
         fldOrderCurrTotal which has the following Control Source
                  =DLookUp("CurrTotal","[qryOrderTotal]","OrderNumber = " & [fldOrderNumber])
      Level 3 sfrmOrderLines contains
            btnCommit onclick is <me.dirty = false:me.requery>
            btnUndo     onclick is  <me.undo:me.requery>
            fldLineNumber   fldPartNumber  Qty   Dollar    
                    01               Nuts             5         $30
                    02               Bolts             5         $20
Question by:rberke
    LVL 38

    Expert Comment

    Use the following test in the appropriate event (e.g. subform close, etc.) that you are trying to prevent.

    If me.dirty then
        MsgBox "you must either commit or undo your change, before proceeding."
    End If
    LVL 16

    Accepted Solution

    Actually it's a there's a little more involved to it than that.  In order to prevent the record from being saved when it is dirty you'll need to cancel the Form's BeforeUpdate Event.  In order to allow the Commit Button to save the record (given that we are keeping the from from being saved any other way) a boolean flag (a modual level variable) will be needed so that the Commit button can throw a switch allowing the record to be saved whereas all other approaches will trigger the error message desired.

    The code required can be found below.

    Option Compare Database
    Option Explicit
    Dim mbooSaveOK As Boolean

    Private Sub cmdCommit_Click()
        If Me.Dirty Then
            mbooSaveOK = True
            Application.RunCommand acCmdSaveRecord
            mbooSaveOK = False
        End If
    End Sub

    Private Sub cmdUndo_Click()
    End Sub

    Private Sub Form_BeforeUpdate(Cancel As Integer)
        If (Me.Dirty) And (mbooSaveOK = False) Then
            Cancel = True
            MsgBox "You must either commit or undo your change, before proceeding", vbCritical, "Commit or Save required"
        End If
    End Sub
    LVL 5

    Author Comment


    That's nearly perfect.

    When the user closes the form, my warning message comes up, then is followed by an Access message

        You can't save this record at this time.

        Microsoft Access may have encoutered an error while trying to save a record.
        If you close this object now, the data changes you made will be lost.
        Do you want to close the database object anyway?
                  YES                 NO

    Q #1 Can I force Access to skip this message and return directly to my form?

    Q#2  I replaced  your routine with the following
        If Me.Dirty Then
            mbooSaveOK = True
            Me.Dirty = False ' << invokes BeforeUpdate, then updates the record
            mbooSaveOK = False
            Me.Requery  < needed to refresh controls in higher level subforms
        End If

    It seems to work fine.  Do you have any comments or preferences between acCmdSaveRecord and my approach?

    Q #3 I still needed me!requery after btnCommit (see q_21782739).  I suppose I could have put it  in afterUpdate. Do you have any opinions?
    LVL 16

    Expert Comment

    Q1) Unfortunately the built in close button for the Parent form will fire off a cascade of events that will result in the messages you described above.  I'm not aware of any way to avoid these messages using the built in close button.

    As an alternative, you could disable the Close button for the form (by changing the Close Button Property to know - you'll find this property on the form's property sheet under the format tab).  Then Add your own close button on the Parent form will the following code behind it...

    Private Sub cmdClose_Click()
        If Me.frmSub.Form.Dirty = False Then
            MsgBox "You must either commit or undo your change, before proceeding", vbCritical, "Commit or Save required"
        End If
    End Sub

    'Using this approach, your user will receive the message about commiting or undoing changes before proceeding and be returned to the sub form without all the static you're receiving from the built in close button.

    Q2) Replacing...
                 Application.RunCommand acCmdSaveRecord
                 Me.Dirty = False
          is perfectly OK.  Me.Dirty = False isn't backwardly compatible to Access 97 but if your working with versions 2K and beyond you're just fine.  

    Q3) Normally this would go in the After Update event but since your design only allows a save from the Commit Button running the Requery from there makes sence (you're basically putting it where the action and it will be very clear as to exactly when the requery method is being run).
    LVL 5

    Author Comment

    Simple advice - easy to follow.  This is why I love EE.  


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 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

    19 Experts available now in Live!

    Get 1:1 Help Now