none
Disable validation of Control when click close button RRS feed

  • Question

  • Please I need help. How do I disable the validation of all controls and close the form without saving?

    I wan the user to be able to cancel the form and not save .

    I have two forms. A main form and a subform embedded.

    I tried using the bolded code but it did not work. It is validating the subform first so, it is collecting the cancel value on the main form.

    Thanks!!!

    This is what I have so far on both forms.

    SUBFORM CODE

    Option Compare Database
    Public CancelForm As Variant
    Private Sub Form_BeforeUpdate(Cancel As Integer)

    *Select Case CancelForm
     *  Case "Cancel"
      *    If MsgBox("Do you want to cancel this update?", vbYesNo) = vbYes Then
          *  Cancel = True
           * DoCmd.Close
            'Exit Sub
          *End If
    *End Select

    If Me.TRANSACTION = "INQUIRY" Then
       
        If IsNull(Me.STATUS) Then
        Cancel = True
        MsgBox ("Please Select Status for the Inquiry."), vbOKCancel
        Me.STATUS.SetFocus
                 End If
     
     ElseIf Me.TRANSACTION = "Protest" Then
            If IsNull(Me.STATUS) Then
            Cancel = True
            MsgBox ("PLEASE SELECT STATUS FOR THE PROTEST."), vbOKCancel
            ElseIf IsNull(Me.DECISION) Then
            Cancel = True
            MsgBox ("PLEASE SELECT DECISION FOR THE PROTEST"), vbOKCancel
            Me.DECISION.SetFocus
            End If
    ElseIf Me.TRANSACTION = "ADJUSTMENT" Then
            If IsNull(Me.APPROVE_AMT) Then
            Cancel = True
            Me.APPROVE_AMT.SetFocus
            MsgBox ("PLEASE ENTER APPROVE AMOUNT FOR ADJUSTMENT."), vbOKCancel
            ElseIf IsNull(Me.DENIED_AMT) Then
            Cancel = True
            MsgBox ("PLEASE ENTER DENY AMOUNT FOR ADJUSTMENT"), vbOKCancel
            Me.DENIED_AMT.SetFocus
     End If

     End If
      
    End Sub
    Private Sub STATUS_BeforeUpdate(Cancel As Integer)
      If Me.TRANSACTION = "Inquiry" Then
        If Me.STATUS = "BEFORE DUE DATE" Then
        Me.DECISION = "FORM SENT"
        ElseIf Me.STATUS = "AFTER DUE DATE" Then
        Me.DECISION = "DENY"
      End If
      End If
    End Sub

    MAINFORM CODE
    Option Compare Database
    Public CancelForm As Variant
    Private Sub CHARGES_subform_Enter()
      If Len(Me.PROTEST_NUMBER & "") = 0 Then
       MsgBox "Please Enter Protest Number To Proceed", vbOKOnly, "Protest Number"
            Cancel = True
    'Me.Parent.Form.SetFocus
    Me.PROTEST_NUMBER.SetFocus
    End If
    End Sub


    Private Sub CloseForm_Click()
    CancelForm= "Cancel"
    End Sub

    Private Sub Form_Current()
    CancelForm= Null
    End Sub

    Private Sub SaveNewEntry_Click()
     'SAVE DOCUMENTS
      On Error GoTo Err_SaveNewEntry_Click
     RunCommand acCmdSaveRecord
     DoCmd.GotoRecord , , acNewRec
    Exit_SaveNewEntry_Click:
    Exit Sub
    Err_SaveNewEntry_Click:
    MsgBox Err.Description
    Resume Exit_SaveNewEntry_Click
    Me.Requery

    End Sub
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim Response As String
    If IsNull(Me.[PROTEST_NUMBER]) Then
    Cancel = True
    MsgBox "Protest Number cannot be empty.Please enter protest Number to proceed!   "
    Me.PROTEST_NUMBER.SetFocus
        ElseIf Nz(DCount("PROTEST_NUMBER", "ADDRESS", "PROTEST_NUMBER = " & Me.PROTEST_NUMBER.Value & ""), vbNullString) > 0 _
        Or Nz(DCount("PROTEST_NUMBER", "CHARGES", "PROTEST_NUMBER = " & Me.PROTEST_NUMBER.Value & ""), vbNullString) > 0 Then
          Cancel = True
    Response = MsgBox("This Number already exists." & vbCrLf & _
                "Please enter a different Number." & vbCrLf & _
                "Click OK to go to the Update Screen." & vbCrLf & _
                "Click Cancel to enter a different Number", _
                vbOKCancel, "DUPLICATE NUMBER")
           
           Me.PROTEST_NUMBER.Undo
            If Response = vbCancel Then
            Cancel = True
      Exit Sub
     Else
      DoCmd.OpenForm "PROTEST_UPDATE", acNormal, , , , , PROTEST_NUMBER.Value
              DoCmd.Close acForm, "NEW_PROTEST_ENTRY"
     End If
          
    Else
      MsgBox "PROTEST NUMBER NOT FOUND"
    End If
    End Sub


    • Edited by Chibut Tuesday, December 13, 2016 6:54 PM
    Tuesday, December 13, 2016 5:24 PM

Answers

  • If you want to abort the insertion of a record after the user has begun to enter data you must first call the Undo method of the form before closing it.  If the user moves focus to the subform then the parent form's current record is saved, subject to any validation criteria, otherwise referential integrity would be violated, so you cannot abort the insertion of a record in the parent form after the user has moved focus to the subform.  You can prevent the user closing the form or moving to another record in the parent form if no record is inserted in the subform, however, forcing the user either to insert at least one record in the subform, or to delete the record in the parent form.  You'll find an example of how to do this in Families.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.

     

    You might also like to take a look at SaveDemo.zip in the same OneDrive folder.  This illustrates how to force the saving and undoing of a record, and the  closing of a form via command buttons.


    Ken Sheridan, Stafford, England

    Tuesday, December 13, 2016 8:32 PM
  • Hi,

    Unfortunately, by the time the code for your button fires, the subform will already be saved. You best option might be to use an unbound form.

    Just a thought...

    Tuesday, December 13, 2016 8:33 PM

All replies

  • If you want to abort the insertion of a record after the user has begun to enter data you must first call the Undo method of the form before closing it.  If the user moves focus to the subform then the parent form's current record is saved, subject to any validation criteria, otherwise referential integrity would be violated, so you cannot abort the insertion of a record in the parent form after the user has moved focus to the subform.  You can prevent the user closing the form or moving to another record in the parent form if no record is inserted in the subform, however, forcing the user either to insert at least one record in the subform, or to delete the record in the parent form.  You'll find an example of how to do this in Families.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.

     

    You might also like to take a look at SaveDemo.zip in the same OneDrive folder.  This illustrates how to force the saving and undoing of a record, and the  closing of a form via command buttons.


    Ken Sheridan, Stafford, England

    Tuesday, December 13, 2016 8:32 PM
  • Hi,

    Unfortunately, by the time the code for your button fires, the subform will already be saved. You best option might be to use an unbound form.

    Just a thought...

    Tuesday, December 13, 2016 8:33 PM
  • I am current working with Form and Subforms.

    How do I prevent my data from saving when I hit the cancel button. The data is saving when I hit the cancel button.

    I also have some validation to check for blank data in the before update event.I need some serious help with this.

    it is a bound form

    thanks!!!

    Save Button Code

    Private Sub SaveNewEntry_Click()
     'SAVE DOCUMENTS
      On Error GoTo Err_SaveNewEntry_Click

     RunCommand acCmdSaveRecord
     DoCmd.GotoRecord , , acNewRec
    Exit_SaveNewEntry_Click:
    Exit Sub
    Err_SaveNewEntry_Click:
    MsgBox Err.Description
    Resume Exit_SaveNewEntry_Click
    Me.Requery

    End Sub

    Cancel Code

    Private Sub CloseForm_Click()
     Cancel = True
            Me.Undo
          
         DoCmd.Close acForm, "NEW_PROTEST_ENTRY"endsub

    Tuesday, December 13, 2016 8:49 PM
  • I wan the user to be able to cancel the form and not save .

    Hi Chibut,

    I am in line with .theDBguy: in my applications all new records are defined in a special "new-record" form with unbound controls.

    After confirmation by the user, and intensive validity checks by the program, the record is stored.

    Imb.

    Tuesday, December 13, 2016 9:53 PM
  • Hi Chibut,

    I find that you already created a thread for the same issue.

    Disable validation of Control when click close button

    in that thread community members had suggested you why your data getting saved because it is bound form and they suggest you to use unbound form.

    so here I want to confirm with you that except that do you have any other issue in this new thread?

    if no then will merge these both threads to avoid the duplication.

    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.

    Wednesday, December 14, 2016 1:57 AM
    Moderator
  • If you are using Bound forms, you're going to have to change your approach since the data is automatically being saved and you'd need to execute a record deletion.

    The other option would be to switch to using an unbound form and then coding Insertions, Updates, ...


    Daniel Pineault, 2010-2016 Microsoft MVP Professional Support: http://www.cardaconsultants.com MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, December 14, 2016 2:22 AM
  • I thought that it was two different issue. You can merge it if they same questions.

    Thanks!!

    Wednesday, December 14, 2016 1:58 PM
  • Thanks for your response. How do I go about using the execute the record deletion?

    I am not a pro in access.

    thanks!!

    Wednesday, December 14, 2016 1:59 PM
  • Thanks everyone!

    I am in a crunch time for this project. So, I am thinking using the unbound might take me a long time . Someone mentioned that I can use the delete statement. How do I go about doing so?

    P.s I am not an access pro. I am in the learning phase. I have never used the unbound.

    Wednesday, December 14, 2016 2:44 PM
  • I think I'm the only respondent who mentioned the word 'delete'.  This was in the context of my Families demo.  In this code in the parent form's Current event procedure checks to see if there are any rows in the Families table without at least one matching row in the FamilyMembers table.  If it finds such a row it navigates to it in the parent form.  So, if a user navigates off such a record in the parent form to another record, the code, which executes when the form is on the next record (or an empty one), will move the form back to the 'childless' record.  The effect of this is that the user can't leave the 'childless' record until they've entered, and saved, a row in the subform.  The 'childless' record is saved at this stage, however.

     

    Code in the parent form's Unload event procedure does the same, but also sets the return value of the procedure's cancel argument to True, which prevents the form from closing.

     

    If the user chooses not to enter a row in the subform for a 'childless' record, or begins to do so and then undoes the insert by pressing the Esc key, the only way the user can close the form or move to another record is by deleting the 'childless' record in the parent form.  This is easily done by clicking on the record selector (the vertical bar on the left side of the form) and pressing the Delete key on the keyboard, or via the Delete option on the ribbon.  Alternatively a command button to delete the current record can be added to the form.  The button wizard can set this up for you or you'll find an example of a customized solution in DeleteDemo.zip in my public databases folder at:

     

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

     


    Ken Sheridan, Stafford, England

    Wednesday, December 14, 2016 5:23 PM
  • Thanks Ken. I am going to look into your link. I am trying to understand all that you have said above.

    I tried coming up with the below before I saw your response:

    The below code It seems to work a little bit. It works if the user goes from the main form to the subform.

    I get the duplicate number message if the user moves from the subform to the main form after they have initially entered the protest number in the mainform.

    I have the duplicate number check in the main form on the beforeupdate event of the form.

    I also setup the cascade delete

    Private Sub CloseRec_Click()
    Dim strSQL As String
    'If Len(Me.PROTEST_NUMBER & "") = 0 Then
    'DoCmd.Close acForm, "NEW_PROTEST_ENTRY"
    If MsgBox("Are you sure that you want to exit the record without saving", vbYesNo, "Closing Dailog") = vbYes Then
     strSQL = "DELETE * FROM ADDRESS WHERE PROTEST_NUMBER = " & Me.PROTEST_NUMBER
     ' Ensure form is not Dirty
     Me.Dirty = False
     ' Delete current row
     CurrentDb.Execute strSQL, dbFailOnError
      DoCmd.Close acForm, "NEW_PROTEST_ENTRY"
      End If
      'Else
      '
     'End If

    End Sub


    • Edited by Chibut Wednesday, December 14, 2016 6:53 PM
    Wednesday, December 14, 2016 6:41 PM
  • I should emphasise that my Families demo is not really about saving or deleting a record, but about enforcing the insertion of at least one row into the referencing table via the subform.  I'm not clear whether this is something you wish to enforce, but if you do it could be relevant.  In my case there is no mechanism to seek user confirmation before attempting to save or delete the parent form's record, and the need to delete it only arises if the user wishes to close the form or move to another record without having inserted a row via the subform.  In essence what you have attempted to do is customize the deletion of the record, whereas mine leaves it to the user to do this via the built in user interface, the need for deletion being a side issue, not what the file is really intended to illustrate.

     

    If you wish to allow the user to delete the parent form's current record after having inserted rows into the referencing table via the subform, then this would simply be a matter of enforcing cascade deletes in the relationship between the two tables.   When your SQL statement deletes the current row from the referenced table any referencing rows would then automatically be deleted also.

     


    Ken Sheridan, Stafford, England

    Wednesday, December 14, 2016 8:47 PM
  • I get the duplicate number message if the user moves from the subform to the main form after they have initially entered the protest number in the mainform.


    The way to avoid that is to test not only for the number existing in a row in the table, but also for the row having a different primary key value to the current row.



    Ken Sheridan, Stafford, England

    Wednesday, December 14, 2016 8:51 PM
  • Thanks everyone!

    I am in a crunch time for this project. So, I am thinking using the unbound might take me a long time . Someone mentioned that I can use the delete statement. How do I go about doing so?

    P.s I am not an access pro. I am in the learning phase. I have never used the unbound.


    Hi, If you can't use an unbound form, then you can let Acces save the record and just change your button code to delete the saved record. Just my 2 cents...
    Thursday, December 15, 2016 1:22 AM
  • Friday, December 16, 2016 3:38 PM
  • Hi,

    Congratulations! Good luck with your project.

    Friday, December 16, 2016 5:28 PM
  • Hi Chibut,

    I have find that from the above given suggestions by the community members you had decided to use unbound form.

    so it looks like your current issue is solved but you did not mark any of the suggestion as an answer.

    the thread is still open.

    so I suggest you to mark the suggestion as an answer which helps you to solve your issue.

    then you can carry on the discussion regarding unbound form in other thread.

    if you do not mark the answer then it will open forever so please mark the answer.

    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.

    Monday, December 19, 2016 7:54 AM
    Moderator