none
Exiting from a form when there are pending updates with validation errors. RRS feed

  • Question

  • I'm modifying an existing application which I did not design or code. Access 2010.

    The main form is a bound form consisting of a tab control with three tabs. Navigation is by the standard MS Access navigation buttons. Every time the form is opened, the entire table of 28,000 records is read in.

    All the data is in one table. It should really be in three different tables since each tabs data is distinct and is validated separately from the other tabs.

    However, I think all that is irrelevant to my question. And I don't have time to redesign or rewrite this application.

    There is a 'return' button on the form that returns to the previous menu. The Before Update event has lots of validations. When the 'return' button is pressed, if the user has made changes, the Before Update event is triggered along with the validations. If there are validation errors, a message box pops up describing all the errors. However, when ok is clicked on this message box, control exits from the form and saves the invalid record.

    My question is how do I prevent the invalid record being saved? I guess I would like to give the user the option to save or discard an invalid record when the 'return' button is pressed. There is currently some code in the Before Update event that displays a message box informing the user when a record has been changed and prompting the user to save or discard the change. This happens only after all the validations have passed.

    Thursday, May 18, 2017 1:15 AM

All replies

  • It would help to see the code in the Before Update event. What is most likely happening is there is a MsgBox command displaying the errors. Following that command, a Cancel = True line is probably needed as well as an Exit Sub to stop processing.

    Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2009 Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

    Thursday, May 18, 2017 12:11 PM
  • Hi AllTheGoodNamesWereTaken,

    I think that this question is some how related with question below which is also asked by you.

    The setting you entered isn't valid for this property - error message

    as other user already mentioned to use "Cancel=True".

    you had mentioned that,"I guess I would like to give the user the option to save or discard an invalid record when the 'return' button is pressed."

    I think that if record is invalid then you should not allow it to Save.

    why need to save invalid data? it will ruin your data and creates lots of issues in the future.

    if you allow to save invalid data then why need to check so many validation? just for asking the user choice.

    I think that you need to spend some time to redesign this saving data approach to save valid data in database.

    it is not good that you have some valid and some invalid data in database. because it can affect you when you try to implement any new functionality to this database in future. also data are not accurate so you will not able to reports properly.

    overall I do not recommend you to save any invalid data to your database.

    if data are invalid then you can warn the user to correct it or cancel it if he don't want to correct it.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 19, 2017 2:45 AM
    Moderator
  • Firstly you should as far as possible include validation rules in the table definition.  You might also be able to apply CHECK CONSTRAINTs to the table for conditions which cannot be covered by a validation rule.

    For validation at form level you not only need to set the return value of the form's BeforeUpdate event procedure's Cancel argument to True, but also that of the Unload event procedure to True to prevent the form closing without the user having the opportunity to correct the validation infringements.  To do this you need to use a Boolean module level variable whose value allows the form to close, and assign a value to this variable which prevents the form closing wherever a validation infringement is encountered elsewhere in the code.  The variable's value needs to be reassigned when the validation infringements have been corrected of course, in order to allow the form to close.

    You might like to take a look at SaveDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this simple little demo file code in the form's module does as described above.  It also ensures that the form can only be closed via a Close button (equivalent to your Return button, but without moving to another form) by enabling/disabling the relevant buttons on the basis of the form's current state.  The form also includes validation at control level.  The full code for the form's module is:

    Option Compare Database
    Option Explicit

    Dim blnSaved As Boolean
    Dim blnAllowClose As Boolean
    Dim blnAllowUndo As Boolean


    Private Sub cboFindContact_AfterUpdate()

        Const CANNOT_MOVE_TO_RECORD = 3709
        Const MESSAGE_TEXT = "Cannot move to record.  Save or undo current record first."
        Dim ctrl As Control
        
        Set ctrl = Me.ActiveControl
        
        If Not IsNull(ctrl) Then
            If ctrl = 0 Then
                Me.FilterOn = False
            Else
                On Error Resume Next
                Me.Filter = "ContactID = " & ctrl
                Select Case Err.Number
                    Case CANNOT_MOVE_TO_RECORD
                    'iinform user and set combo box to current contact
                    MsgBox MESSAGE_TEXT, vbInformation, "Warning"
                    ctrl = Me.ContactID
                    Case 0
                    ' no error
                    Me.FilterOn = True
                    Case Else
                    ' unknown error - inform user
                    MsgBox Err.Description, vbExclamation, "Error"
                End Select
            End If
        End If
            
    End Sub


    Private Sub cmdClose_Click()

        ' set variable to allow form to close
        blnAllowClose = True
        DoCmd.Close acForm, Me.Name
        
    End Sub

    Private Sub cmdSave_Click()
       Const MESSAGETEXT = "Save record?"

       If Me.Dirty Then
           ' if user confirms set variable to True and attempt to save record
           If MsgBox(MESSAGETEXT, vbQuestion + vbYesNo, "Confirm") = vbYes Then
               blnSaved = True
               On Error Resume Next
               RunCommand acCmdSaveRecord
               ' if record cannot be saved set variable to False
               If Err <> 0 Then
                   blnSaved = False
               Else
                   ' disable buttons
                   ' move focus to another control first
                   Me.FirstName.SetFocus
                   Me.cmdSave.Enabled = False
                   Me.cmdUndo.Enabled = False
                   Me.cmdClose.Enabled = True
               End If
           Else
               blnSaved = False
           End If
       End If

    End Sub

    Private Sub cmdUndo_Click()

        blnAllowUndo = True
        ' undo edits
        Me.Undo
        blnAllowUndo = False
        ' disable buttons
        ' move focus to another control first
        Me.FirstName.SetFocus
        Me.cmdSave.Enabled = False
        Me.cmdUndo.Enabled = False
        Me.cmdClose.Enabled = True

    End Sub



    Private Sub DoB_GotFocus()

        If IsNull(Me.FirstName) Then
            MsgBox "First name must be entered", vbExclamation, "Invalid Operation"
            Me.FirstName.SetFocus
        ElseIf IsNull(Me.Lastname) Then
            MsgBox "Last name must be entered", vbExclamation, "Invalid Operation"
            Me.Lastname.SetFocus
        End If

    End Sub

    Private Sub FirstName_BeforeUpdate(Cancel As Integer)

        If IsNull(Me.FirstName) Then
            MsgBox "First name must be entered", vbExclamation, "Invalid Operation"
        End If

    End Sub

    Private Sub Form_AfterUpdate()

       ' reset variable to False
       blnSaved = False
       
    End Sub



    Private Sub Form_BeforeUpdate(Cancel As Integer)

        Dim strMessage As String
        Dim strCriteria As String

        ' cancel update if variable is False,
        ' i.e. save button has not been clicked
        If Not blnSaved Then
           Cancel = True
           Exit Sub
        End If
        
        ' ensure first and last names have been entered
        If IsNull(Me.FirstName) Then
            MsgBox "First name must be entered", vbExclamation, "Invalid Operation"
            Cancel = True
            Me.FirstName.SetFocus
            Exit Sub
        End If
       
        If IsNull(Me.Lastname) Then
            MsgBox "Last name must be entered", vbExclamation, "Invalid Operation"
            Cancel = True
            Me.Lastname.SetFocus
            Exit Sub
        End If
        
        ' ensure names not duplicated
        strCriteria = "FirstName = """ & Me.FirstName & _
            """ And LastName = """ & Me.Lastname & _
            """ And ContactID <> " & Me.ContactID
            
        If Not IsNull(DLookup("ContactID", "Contacts", strCriteria)) Then
            strMessage = Me.FirstName & " " & Me.Lastname & _
                " already exists in database."
            
            MsgBox strMessage, vbExclamation, "Invalid Opeartion"
            Cancel = True
        End If
        

    End Sub

    Private Sub Form_Current()

       ' reset variable to False
       blnSaved = False
       ' disable buttons
       ' move focus to another control first
       Me.FirstName.SetFocus
       Me.cmdSave.Enabled = False
       Me.cmdUndo.Enabled = False
       Me.cmdClose.Enabled = True

    End Sub


    Private Sub Form_Dirty(Cancel As Integer)

       ' enable buttons
       Me.cmdSave.Enabled = True
       Me.cmdUndo.Enabled = True
       Me.cmdClose.Enabled = False

    End Sub


    Private Sub Form_Error(DataErr As Integer, Response As Integer)

       Const IS_DIRTY = 2169
       Const MESSAGETEXT = "Record will not be saved."
       
       ' suppress system error message if form
       ' is closed while record is unsaved,
       ' NB: changes to current record will be lost
       If DataErr = IS_DIRTY Then
            MsgBox MESSAGETEXT, vbExclamation, "Warning"
            Response = acDataErrContinue
       End If

    End Sub

    Private Sub Form_Undo(Cancel As Integer)

        ' only allow record to be undone via Undo button
        Cancel = Not blnAllowUndo
         
    End Sub

    Private Sub Form_Unload(Cancel As Integer)

        ' prevent closure if not via button
        Cancel = Not blnAllowClose
        
    End Sub

    Private Sub Lastname_BeforeUpdate(Cancel As Integer)

        If IsNull(Me.Lastname) Then
            MsgBox "Last name must be entered", vbExclamation, "Invalid Operation"
        End If

    End Sub

    Private Sub Lastname_GotFocus()

        If IsNull(Me.FirstName) Then
            MsgBox "First name must be entered", vbExclamation, "Invalid Operation"
            Me.FirstName.SetFocus
        End If

    End Sub


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Friday, May 19, 2017 11:18 AM Hyperlink added.
    Friday, May 19, 2017 11:17 AM