CABHugh
asked on
Retain changes in "Before Update" if validation fails
This is probably an old chestnut. For various reasons we need to retain the Close "X" button on a form
If the User is in the middle of an edit and clicks close, they get the option to save the record. The validation therefore has to be called from the "Before Update" process.
If validation fails, we use "Cancel = True" so the record is not saved and return them to the form.
However "Cancel = True" removes all of their changes.
How do I retain the changes and give them the ability to fix them before they exit?
I really need an "On Before Close" trigger that is called before the "On Before Update".
Having struggled with this all weekend I would really appreciate some help.
Regards
Hugh
If the User is in the middle of an edit and clicks close, they get the option to save the record. The validation therefore has to be called from the "Before Update" process.
If validation fails, we use "Cancel = True" so the record is not saved and return them to the form.
However "Cancel = True" removes all of their changes.
How do I retain the changes and give them the ability to fix them before they exit?
I really need an "On Before Close" trigger that is called before the "On Before Update".
Having struggled with this all weekend I would really appreciate some help.
Regards
Hugh
Cancel = True won't undo user changes ... it should simple stop the process and allow you to take other actions.
Can you post the code included in the form's BeforeUpdate event?
Can you post the code included in the form's BeforeUpdate event?
ASKER
Andrew,
Hi.
Nice idea, but "On Unload" only fires after Access has finished with the "On before Update" and "On After Update". By this time, the record has either been written or the changes have been "discarded".
I need to apply the validation and then stop the update without losing the changes, thereby allowing the User to fix any errors or abondon the update (at their decision, not Access's).
Hugh
Hi.
Nice idea, but "On Unload" only fires after Access has finished with the "On before Update" and "On After Update". By this time, the record has either been written or the changes have been "discarded".
I need to apply the validation and then stop the update without losing the changes, thereby allowing the User to fix any errors or abondon the update (at their decision, not Access's).
Hugh
The best way, to me, is to take more control over the process.
1) Add a Form-level variable in the General Declarations section:
Private bSave As Boolean
2) In the BeforeUpdate event:
Sub Form_BeforeUpdate(Cancel As Integer)
'/if the user hasn't clicked Save, we still want to run the validate routine
If Not bSave Then
If <validate data> Then
'/everything okay, just save the data
Me.Dirty = False
Else
Msgbox "Your data did not validate"
Cancel = True
End If
End If
End Sub
3) Now add a Save button to your form that validates and sets the bSave varialbe:
Function SaveMe() As Boolean
'/run your validation routine
If <your validation is okay) Then
bSave = True
'/save the data
If Me.Dirty Then Me.Dirty = False
End If
End Function
4) Reset bSave when user moves to new record
Sub Form_Current()
bSave = True
End Sub
Note that it would be best if you move your validation routine to a separate function so that you can call it from various places as needed.
1) Add a Form-level variable in the General Declarations section:
Private bSave As Boolean
2) In the BeforeUpdate event:
Sub Form_BeforeUpdate(Cancel As Integer)
'/if the user hasn't clicked Save, we still want to run the validate routine
If Not bSave Then
If <validate data> Then
'/everything okay, just save the data
Me.Dirty = False
Else
Msgbox "Your data did not validate"
Cancel = True
End If
End If
End Sub
3) Now add a Save button to your form that validates and sets the bSave varialbe:
Function SaveMe() As Boolean
'/run your validation routine
If <your validation is okay) Then
bSave = True
'/save the data
If Me.Dirty Then Me.Dirty = False
End If
End Function
4) Reset bSave when user moves to new record
Sub Form_Current()
bSave = True
End Sub
Note that it would be best if you move your validation routine to a separate function so that you can call it from various places as needed.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
LMS Consulting,
Hi.
Cancel = True appears to be the cause of the changes being removed. I also saw a blog over the weekend which seemed to confirm this.
I am hoping you are correct, but stepping through the Debug, the changes vanish as soon as the "Before Update" process exits. I will attach the code snippet.
flgValidExit - is set to True if the User uses the correct "End Edit" button. This is required because clicking the Close "X" box goes directly to "On Before Update" and the validation therefore has to be triggered in that procedure.
flgSaveOK - is set to True at the start of the validate and is set to False if any errors occur. the User gets a list of all invalid fields in one MsgBox to avoid repeated attempts at saving the record.
Regards
Hugh
Hi.
Cancel = True appears to be the cause of the changes being removed. I also saw a blog over the weekend which seemed to confirm this.
I am hoping you are correct, but stepping through the Debug, the changes vanish as soon as the "Before Update" process exits. I will attach the code snippet.
flgValidExit - is set to True if the User uses the correct "End Edit" button. This is required because clicking the Close "X" box goes directly to "On Before Update" and the validation therefore has to be triggered in that procedure.
flgSaveOK - is set to True at the start of the validate and is set to False if any errors occur. the User gets a list of all invalid fields in one MsgBox to avoid repeated attempts at saving the record.
Regards
Hugh
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not flgValidExit Then
ConfirmSave
If flgSaveOK = False Then
Cancel = True
End If
End If
End Sub
ASKER
LMS Consulting.
We crossed in our comments, but we seem to be of a like mind. My code appears to address similar situations to yours.
Hugh
We crossed in our comments, but we seem to be of a like mind. My code appears to address similar situations to yours.
Hugh
What's in ConfirmSave?
ASKER
LMS Consulting
I have attached 2 Procedures. ConfirmSave calls ValidateFields.
hugh
I have attached 2 Procedures. ConfirmSave calls ValidateFields.
hugh
Private Sub ConfirmSave()
'Confirms user wants to save and then validates fields
'The variable flgDatavalid is set to True if all validated fields are valid
flgFormOpen = True
flgSaveOK = True
If Me.Dirty Then
If MsgBox("Do you wish to save these changes?", vbQuestion + vbYesNo, "Save...") = vbNo Then
Form.Undo
If RepairProcessType = "AddNewRepair" Or RepairProcessType = "EditRepair" Then
'If SysCmd(acSysCmdGetObjectState, acForm, "frmCLIENTS") = 1 Or SysCmd(acSysCmdGetObjectState, acForm, "frmSTOCK") = 1 Then
DoCmd.Close acForm, "frmRepairs"
flgSaveOK = True
flgFormOpen = False
Exit Sub
End If
Else
flgDataValid = True
ValidateFields
If flgDataValid Then
If flgValidExit Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
flgSaveOK = True
Me.Refresh
End If
Else
flgSaveOK = False
End If
End If
Else
If RepairProcessType = "Addnewrepair" Or RepairProcessType = "EditRepair" Then
If flgValidExit = True Then
DoCmd.Close acForm, "frmRepairs"
End If
flgSaveOK = True
Me.Refresh
flgFormOpen = False
Exit Sub
End If
End If
End Sub
Private Sub ValidateFields()
strMess = ""
If (IsNull(Me.noncabname) Or Me.noncabname = "") And (IsNull(Me.ClientFullName) Or Me.ClientFullName = "") Then
strMess = " The name of the NON-CAB Client." & vbCrLf
End If
If IsNull(Me.description) Then
strMess = strMess & " A Repair Description." & vbCrLf
End If
If IsNull(Me.Serial1) Then
strMess = strMess & " A Serial Number." & vbCrLf
End If
If IsNull(Me.SentForRepairDate) Then
strMess = strMess & " The Date that the Repair was sent by the Branch." & vbCrLf
End If
If IsNull(Me.FromBranch) Then
strMess = strMess & " The Branch which sent the Repair." & vbCrLf
End If
If IsNull(Me.Supplier) Then
strMess = strMess & " The name of the company which will make the Repair." & vbCrLf
End If
If strMess <> "" Then
flgDataValid = False
strMess = "Please Enter:" & vbCrLf & strMess
MsgBox strMess
End If
End Sub
A quick read of the BeforeUpdate event section in Help shows you're correct:
"If you cancel an update, the value of the OldValue property replaces the existing value in the control."
So the event will undo user changes, and revert back to the original values ...
"If you cancel an update, the value of the OldValue property replaces the existing value in the control."
So the event will undo user changes, and revert back to the original values ...
ASKER
LMS Consulting
I hope that this is not a Showstopper. There must be thousands of Users worldwide who need the same facility.
I wonder if anybody has a workaround.
Hugh
I hope that this is not a Showstopper. There must be thousands of Users worldwide who need the same facility.
I wonder if anybody has a workaround.
Hugh
Further, I'm not sure how you'd do this using a bound form ... even if you store the user info in an arrar or UDT and write them back, this would just fire the event again I believe. You could try doing this; just store the values of the form in module-level variables, and write them back AFTER you cancel ... this may or may not work, so make sure to set a breakpoint.
FWIW, I long ago moved to unbound forms for almost all of my applications. This provides full control over the data, and allows me to handle things such as this very easily.
FWIW, I long ago moved to unbound forms for almost all of my applications. This provides full control over the data, and allows me to handle things such as this very easily.
I just tried this, and it seemed to work, i.e. didn't undo the user changes:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not flgValidExit Then
ConfirmSave
If flgSaveOK = False Then
Cancel = True
Exit Sub
End If
End If
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not flgValidExit Then
ConfirmSave
If flgSaveOK = False Then
Cancel = True
Exit Sub
End If
End If
End Sub
ASKER
LMS Consulting
I will give it a try.
Hugh
I will give it a try.
Hugh
ASKER
LMS Consulting
I added the Exit sub immediately below the "Cancel = True".
As soon as it obeyed the Exit sub the changes disappeared.
I wonder what is different between your version and mine?
We are still running Access 2003.
Hugh
I added the Exit sub immediately below the "Cancel = True".
As soon as it obeyed the Exit sub the changes disappeared.
I wonder what is different between your version and mine?
We are still running Access 2003.
Hugh
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi.
I have to go to a meeting to schedule the next phase of the Database Development.
I will try your suggestion after the meeting.
I am sorry for the interuption.
Thank you very much for your time this morning.
Hugh
I have to go to a meeting to schedule the next phase of the Database Development.
I will try your suggestion after the meeting.
I am sorry for the interuption.
Thank you very much for your time this morning.
Hugh
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Whoops.
Just looked at the code and realised all the popup messages are constants.
I usually create a Module to store all the constants so I can change them quickly.
Here's the ones for the msgboxes used in the form
Just looked at the code and realised all the popup messages are constants.
I usually create a Module to store all the constants so I can change them quickly.
Here's the ones for the msgboxes used in the form
Public Const Con_ErrorSaveTitle = "Save Required"
Public Const Con_ErrorSaveTxt = "Please Save This Record!" & vbCrLf & vbLf & "You can not close this form until you either 'Save' the changes made to this record or 'Undo' your changes."
Public Const Con_ErrorSaveIcon = vbExclamation
Public Const Con_ErrorNoRecUndoTxt = "There were no modifications made to the current record."
Public Const Con_ErrorNoRecUndoIcon = vbInformation
Public Const Con_ErrorNoRecUndoTitle = "Can't Undo"
Public Const Con_ErrorRecNotSavedTxt = "Please Save This Record!" & vbCrLf & vbLf & "You can not advance to another record until you either 'Save' the changes made to this record or 'Undo' your changes."
Public Const Con_ErrorRecNotSavedIcon = vbExclamation
Public Const Con_ErrorRecNotSavedTitle = "Save Required"
Public Const Con_QuSaveRecTxt = "Do you want to save your changes to the current record?" & vbCrLf & vbLf & " Yes: Saves changes" & vbCrLf & " No: Continue editing record" & vbCrLf & " Cancel: Undo changes" & vbCrLf
Public Const Con_QuSaveRecIcon = vbYesNoCancel + vbQuestion
Public Const Con_QuSaveRecTitle = "Save Current Record?"
ASKER
To LSMConsulting, ClarkScott, LPurvis, Ozinm, badotz and the other people who took the time to assist me, I apologise for the delay in closing this. I am particularly indebted to LSMConsulting for going the extra mile, but the contributions have been useful.
I have decided to throw in the towel and compromise our development standards in the interests of saving time and moving on. I have placed a big "Close" button near the "X" and removed the "X". The user is prompted to click the other visible button which completes their Edit or AddNew Process where the Validation and option to cancel is handled.
We also have a "Close" button on the Menu bar. if they click this their changes are lost, but they get a message telling them what they did and suggesting what they should do in the future. "A training exercise" as suggested by badotz.
The Users seem happy with the compromise.
Thanks again.
Hugh
I have decided to throw in the towel and compromise our development standards in the interests of saving time and moving on. I have placed a big "Close" button near the "X" and removed the "X". The user is prompted to click the other visible button which completes their Edit or AddNew Process where the Validation and option to cancel is handled.
We also have a "Close" button on the Menu bar. if they click this their changes are lost, but they get a message telling them what they did and suggesting what they should do in the future. "A training exercise" as suggested by badotz.
The Users seem happy with the compromise.
Thanks again.
Hugh
No worries - glad to help.
Cheers, Andrew