Require use of Undo or Commit button

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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rberkeConsultantAuthor Commented:

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?
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).
rberkeConsultantAuthor Commented:
Simple advice - easy to follow.  This is why I love EE.  

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.