?
Solved

Retain changes in "Before Update" if validation fails

Posted on 2008-01-28
23
Medium Priority
?
471 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:CABHugh
  • 9
  • 7
  • 2
  • +4
23 Comments
 
LVL 28

Expert Comment

by:TextReport
ID: 20758276
Have you tried the On Unload rather than the On Close?
Cheers, Andrew
0
 
LVL 85
ID: 20758285
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?
0
 

Author Comment

by:CABHugh
ID: 20758295
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
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 85
ID: 20758315
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.
0
 
LVL 29

Assisted Solution

by:Badotz
Badotz earned 150 total points
ID: 20758320
Perhaps this is best addressed as a "training issue", with dire warnings re: missing data, etc.? I mean, if they click the [X], they *really* want to quit, eh?
0
 

Author Comment

by:CABHugh
ID: 20758324
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

0
 

Author Comment

by:CABHugh
ID: 20758332
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
0
 
LVL 85
ID: 20758351
What's in ConfirmSave?
0
 

Author Comment

by:CABHugh
ID: 20758365
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

0
 
LVL 85
ID: 20758368
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 ...
0
 

Author Comment

by:CABHugh
ID: 20758391
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
0
 
LVL 85
ID: 20758395
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.
0
 
LVL 85
ID: 20758443
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
0
 

Author Comment

by:CABHugh
ID: 20758461
LMS Consulting

I will give it a try.

Hugh
0
 

Author Comment

by:CABHugh
ID: 20758514
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

0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 750 total points
ID: 20758563
I'm running Access 2003 SP2.

Are you currently showing the RecordSelector on your form? If not, enable it, then run the code again with the Exit Sub in place, and see if the "pencil" still is showing after you exit the BeforeuPdate routine ... in my db, the pencil is still showing (meaning Access is still editing the record).
0
 
LVL 20

Assisted Solution

by:clarkscott
clarkscott earned 225 total points
ID: 20758583
I do things like this 'manually'.  The following is a little 'wordy' but it's versitile.
Create a collection of all the fields on the form.  Populate the collection in the 'On_Current' event of the form.  This will remember all your initial values and you can do with it what you want.

In the forms DECLARATION section:
dim vCol as new collection

Create a "Remove" function to clear the collection:

sub vCol_Remove()
    on error resume next
     vCol.remove "YourFieldname1"
     vCol.remove "YourFieldName2"
    .   and so on
end sub

In the On_Current event:
  if not isnull(me.YourField1) then
         vCol.Add YourField1name1.value, "YourFieldName1"
  else
        vcol.Add { 0   or "" depending on field type ), "YourFieldName2"
end if

if not isnull(me.yourfield2) then
       vCol.add YourFieldName2.value, "YourFieldName2"
else
       vCol.add "", "yourfieldname2"
end if

and so on.........

You now have a complete copy of your original data.  You can validate the new changes, and if necessary, replace any new fields with values from your collection.

me.field1 = vCol("Fieldname1")

Remember, a collection can be a combination of any type variables - not like an array.

Scott C


0
 

Author Comment

by:CABHugh
ID: 20758588
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
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 150 total points
ID: 20758637
I know that's what the Help File might be saying - but it isn't what actually happens.  (The Help has been wrong - or at least made misleading implications - before... Even though I actually think it's perfectly sufficient most of the time - I've never quite understood the dislike of it.)

However *if* you're closing the form immediately after this action then yes - you'll receive and error message about being unable to Save and it'll close removing changes.  (If you're supressing error messages by whatever means then that part will be skipped of course).

This is separate to the BeforeUpdate being cancelled.  Indeed that event has done it's job - it's prevented the Update from occuring.  The fact that the form then closes leaves it with no other choice than to close with the non-updated data.
What you actually want to do is prevent the form from closing.  (Hiding it might be all you need.)

If you have a look at the examples page (linked to in my profile) you'll see the first example "Cancel Undo".
This demonstrates using BeforeUpdate and its cancelling, along with closing, in bound forms.
If you're so inclined - there's also an Unbound Form example towards the bottom (as Scott mentioned) - if you need further control.
If you need an (effectively - though obviously not fully... it's impossible) unbound continuous/subform then there's an example at the very bottom of the page.
0
 

Assisted Solution

by:ozinm
ozinm earned 225 total points
ID: 20758778
Hi ya,
I normally do something simliar on my forms forcing people to save their changes or discarding them.
NB: this also traps people paging through records as well.

I'm pretty sure you could modifiy it to suit your needs.

1. create a checkbox called: HiddenProperSave
2. on HiddenProperSave set the Visible property to No
3. create 3 buttons: cmdSave, cmdUndo, cmdClose
4. paste the attached code into your form

NB: I've been using this for a long while but can't take credit for the original code (although I may have altered it a bit over the years). I believe the original came from the code repository at http://www.access-programmers.co.uk/forums/forumdisplay.php?f=63

All the best


Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
 
    'Prompts the user to save the current record if it needs to be saved.
    If Me.Dirty Then
        Beep
        MsgBox Con_ErrorSaveTxt, Con_ErrorSaveIcon, Con_ErrorSaveTitle
    Else
        DoCmd.Close acForm, Me.Name
    End If
 
Exit_cmdClose_Click:
    Exit Sub
 
Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
End Sub
 
 
 
Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
 
    'Place any final user entry error checking here
    
    
    
    'End of final error checks
 
    Select Case MsgBox(Con_QuSaveRecTxt, Con_QuSaveRecIcon, Con_QuSaveRecTitle)
        Case vbYes: 'Save the changes
            Me.HiddenProperSave.Value = True
            DoCmd.RunCommand acCmdSaveRecord
 
        Case vbNo: 'Do not save or undo
            'Do nothing
 
        Case vbCancel: 'Undo the changes
            DoCmd.RunCommand acCmdUndo
            Me.HiddenProperSave.Value = False
 
        Case Else: 'Default case to trap any errors
            'Do nothing
 
    End Select
 
Exit_cmdSave_Click:
    Exit Sub
 
Err_cmdSave_Click:
    If Err = 2046 Then 'The command or action Undo is not available now
        Exit Sub
    Else
        MsgBox Err.Description
        Resume Exit_cmdSave_Click
    End If
End Sub
 
 
 
 
Private Sub cmdUndo_Click()
On Error GoTo Err_cmdUndo_Click
 
    'Resets the record if it has been modified by the user.
    If Me.Dirty Then
        Beep
        DoCmd.RunCommand acCmdUndo
        Me.HiddenProperSave.Value = False
    Else 'The command or action Undo is not available now
        Beep
        MsgBox Con_ErrorNoRecUndoTxt, Con_ErrorNoRecUndoIcon, Con_ErrorNoRecUndoTitle
    End If
 
Exit_cmdUndo_Click:
    Exit Sub
 
Err_cmdUndo_Click:
    MsgBox Err.Description
    Resume Exit_cmdUndo_Click
    
End Sub
 
 
 
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate
        
    If Not Me.HiddenProperSave.Value Then
        MsgBox Con_ErrorRecNotSavedTxt, Con_ErrorRecNotSavedIcon, Con_ErrorRecNotSavedTitle
        DoCmd.CancelEvent
        Exit Sub
    End If
 
Exit_Form_BeforeUpdate:
    Exit Sub
 
Err_Form_BeforeUpdate:
    If Err = 3020 Then  'Update or CancelUpdate without AddNew or Edit
        Exit Sub
    Else
        MsgBox Err.Description
        Resume Exit_Form_BeforeUpdate
    End If
End Sub
 
 
 
Private Sub Form_Current()
    Me.HiddenProperSave.Value = False ' Set the initial edit status
End Sub
 
 
 
Private Sub Form_Dirty(Cancel As Integer)
   ' If you have a modified date field that needs updating enable this line:
   ' ModifiedDate = Now() ' update the modifed date
End Sub

Open in new window

0
 

Expert Comment

by:ozinm
ID: 20758844
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

0
 

Author Closing Comment

by:CABHugh
ID: 31425593
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
0
 
LVL 29

Expert Comment

by:Badotz
ID: 20777328
No worries - glad to help.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…
Suggested Courses

589 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