Problem validating form input when user clicks Close button

Question

How should I code the processing when a user makes changes on the form that are invalid and then clicks the Close button. I don't seem to be able to get it to display the errors and remain on the current form wthout running an Undo and clearing out and data entered. I have the validations in Form_Update and set Cancel = True if I find errors but control is still trying to exit the form so in Unload_Form event I set Cancel = True again. Is this right? Still I am seeing an UnDo event fire and data entered get cleared out. Everything works fine if the user makes invalid changes and then navigates to another record. I only have this problem when the Close button is clicked.

Answer 1

You could prevent the user from closing the form by clicking the "x" in the upper right corner:

Private blnCanClose As Boolean

Private Sub cmdClose_Click()
    ' Test for invalid data
    If ... Then
        Exit Sub
    End If
    blnCanClose = True
    DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If blnCanClose = False Then
        MsgBox "Please use the Close command button to close the form", _
            vbExclamation
        Cancel = True
    End If
End Sub

---

Regards, Hans Vogelaar

Answer 2

Hello,

Try Cancel=1

Nadia

Answer 3

You might care to take a look at the file SaveDemo*.zip in my public databases folder at:

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

This simple demo file includes validation on the two name controls and forces the user to save the record via a command button.  Similarly the form can only be closed via a command button which is only made available after the record has been successfully saved or undone.

---

Ken Sheridan, Stafford, England

Answer 4

I already have the X disabled. The problem has nothing to do with that, its the close button (command button) that I have provided to close the form.

Answer 5

I looked at your database example. I had not thought about having a separate Save button. My form is considerably more complex than your example, for instance I have navigation controls that allow the user to go the the first, last, previous or next record in the search list from which my form is opened. Currently, the save is automatic when navigating to a different record or closing the form. It will take some thought to adapt your solution to my circumstances.

Answer 6

I had not thought about having a separate Save button.

Having struggled on many occasions with the same sort of problem you are having when closing a form, I've come to the conclusion that it's the easiest solution.  The navigation controls should not be big problem, you'd enable/disable them in the same places as the code in my demo disables the Close button.

---

Ken Sheridan, Stafford, England