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:
fldOrderNumber,
fldOrderShipAddress
fldOrderCurrTotal which has the following Control Source
=DLookUp("CurrTotal","[qry
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
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:
fldOrderNumber,
fldOrderShipAddress
fldOrderCurrTotal which has the following Control Source
=DLookUp("CurrTotal","[qry
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
rick
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?
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
DoCmd.Close
Else
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
with...
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).
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
DoCmd.Close
Else
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
with...
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).
ASKER
Simple advice - easy to follow. This is why I love EE.
If me.dirty then
MsgBox "you must either commit or undo your change, before proceeding."
End If