Link to home
Start Free TrialLog in
Avatar of CABHugh
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
Avatar of TextReport
TextReport
Flag of United Kingdom of Great Britain and Northern Ireland image

Have you tried the On Unload rather than the On Close?
Cheers, Andrew
Avatar of Scott McDaniel (EE MVE )
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?
Avatar of CABHugh
CABHugh

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
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.
SOLUTION
Avatar of Badotz
Badotz
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of CABHugh

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
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not flgValidExit Then
   ConfirmSave
   If flgSaveOK = False Then
      Cancel = True
   End If
End If
End Sub

Open in new window

Avatar of CABHugh

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
What's in ConfirmSave?
Avatar of CABHugh

ASKER

LMS Consulting

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

Open in new window

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 ...
Avatar of CABHugh

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
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.
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
Avatar of CABHugh

ASKER

LMS Consulting

I will give it a try.

Hugh
Avatar of CABHugh

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

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of CABHugh

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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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?"

Open in new window

Avatar of CABHugh

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
No worries - glad to help.