Require use of Undo or Commit button

Posted on 2006-03-21
Medium Priority
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
  • 2
  • 2
LVL 38

Expert Comment

ID: 16250343
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

Rick_Rickards earned 500 total points
ID: 16250808
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

Author Comment

ID: 16252590

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

ID: 16252987
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).

Author Comment

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


Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

850 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