How to detect "X" clicked on a Form ?

Question

_{Sunday, July 5, 2015 11:57 AM}

We want to run some code if the user closes a form by clicking the "X" in the upper right hand corner.

I can't find that as one of the forms "Events"

Thanks for any help on how do do this.

All replies (18)

_{Sunday, July 5, 2015 4:26 PM ✅Answered}

As added in your other question, Me.Undo not working in Close Event - Record gets added anyway, I think the answer you are looking for is as follows:

Option Compare Database
Option Explicit

Dim mUpdateOK As Boolean

Private Sub cmdCancel_Click()
    
    Me.Undo
    DoCmd.Close
    
End Sub

Private Sub cmdClose_Click()
    
    mUpdateOK = True
    DoCmd.Close
    
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    On Error GoTo Err_Process
    
    If (Not mUpdateOK) Then
        ' Prompt to confirm the save operation.
        If MsgBox("Changes have not been saved.  Do you want to save?", vbYesNo + vbQuestion, "Save Record") = vbNo Then
            Me.Undo
        End If
    End If
    mUpdateOK=False
Exit_Process:
    Exit Sub

Err_Process:
    MsgBox "Procedure: Form_BeforeUpdate" & vbCrLf & vbCrLf & Err.Number & " " & Err.Description, vbExclamation, "Error"
    Resume Exit_Process
    
End Sub

_{Sunday, July 5, 2015 12:36 PM}

In an effort to prevent a record from being added if the user clicks the "X" in an Add_New_Record form we have tried this.

Create a hidden label on the form with...

Name = saveClicked
Caption = saveNo

If the user clicks the Save and Close button on the form we change the caption to saveYes

If the user clicks the "X" on the upper right of the form to close the form we look at the saveClicked Caption and if it is still "saveNo" then we execute Me.Undo

But the record still saves.

This is a mystery because Me.Undo works when we run it for the Close Without Saving button on the form.

Close Event Code:

Private Sub Form_Close()
If Me.saveClicked.Caption = "saveNo" Then
    Me.Undo
End If
End Sub

Close Without Saving button code:

Private Sub Command20_Click()
Me.Undo
End Sub

I'm sure there is a better way to do this and please share it with me if you know it but... I also want to know why the approach we took is not working.

Thanks for any help.

_{Sunday, July 5, 2015 12:38 PM}

Either use On Close or On Unload.

_{Sunday, July 5, 2015 1:40 PM}

On Close fires no matter how the form is closed.

We are trying to detect that the form was closed by clicking the "X" in the upper left corner of the form window.

_{Sunday, July 5, 2015 2:02 PM}

There's no reliable way to detect this..

btw, why do you want to do this?

_{Sunday, July 5, 2015 2:23 PM}

I assume that you have a close button that you would prefer to be used rather than the [X]. As Steve has already mentioned, there is no method for determining how the form was closed. What you can do is determine if the form was closed using your button, otherwise it was closed using the [X] or [Ctrl]+[F4].

The following procedures will prevent the user from closing the form any other way except by using your close button.

Option Compare Database
Option Explicit

Dim mCloseExecuted As Boolean

Private Sub cmdClose_Click()
    
    mCloseExecuted = True
    DoCmd.Close
    
End Sub

Private Sub Form_Unload(Cancel As Integer)

    If (Not mCloseExecuted) Then
        Cancel = True
        mCloseExecuted = False
        MsgBox "You must use the close button to close this form.", vbExclamation, "Error"
    End If

End Sub

_{Sunday, July 5, 2015 2:30 PM}

This answer you are looking for is in your other question: How to detect "X" clicked on a Form ?.

Alternatively, you can disable the [X] button in the form's properties: Format>Close Button: No. However, this will not prevent the user from using [Ctrl]+[F4], unless the form's property: Other>Pop Up is set to Yes.

_{Sunday, July 5, 2015 2:41 PM}

Hi,

Just curious, try using two Me.Undo in succession to see if that helps.

_{Sunday, July 5, 2015 3:15 PM}

By the time the form's Close event fires, the record has already been saved. Use the Unload event instead.

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

_{Sunday, July 5, 2015 3:50 PM}

This makes no sense. Why restricting the user that way?

The only reason to do it this way would be that you need to runs some code when the form is closed. But then you can place that code right into the On Close event. This way the user has two methods of closing the form. Which is better than restricting him.

_{Sunday, July 5, 2015 3:57 PM}

By the time the form's Close event fires, the record has already been saved. Use the Unload event instead.

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Good point! If this is for data validation, it's best to use the form's BeforeUpdate event. Otherwise, like Dirk said, the Close event is too late. Cheers!

_{Sunday, July 5, 2015 4:19 PM}

We have one table... tStuff

There is a Master Menu with a "Create New Record" button

That opens a data entry form fAddRecord with all the records fields and two buttons
Close and Save
Close No Save

The code we had was creating the record even if the user did not enter any data so we added the "Close No Save" button

But the user could still click the "X" in the forms upper right corner... which would result in the blank record being saved in the table...

Below is what we have now and it seems to be working... but we had to add code RunningManHD's code to delete the last record... is there a better way?

Dim mCloseExecuted As Boolean

Private Sub Command19_Click() 'Button Close & Save Clicked
    mCloseExecuted = True
    DoCmd.Close acForm, "fAddRecord", acSaveYes
End Sub

Private Sub Command20_Click() 'Button Close No Save Clicked
    Me.Undo
    mCloseExecuted = True
    DoCmd.Close acForm, "fAddRecord"
End Sub

Private Sub Form_Unload(Cancel As Integer) 'unload event    If (Not mCloseExecuted) Then
        Cancel = True
        mCloseExecuted = False
        MsgBox "Nope", vbExclamation, "Error"
        'now delete the record - is there a better way?
        Dim rs As DAO.Recordset
        Set rs = CurrentDb.OpenRecordset("tStuff", dbOpenTable)
        rs.MoveLast
        rs.Delete
        'rs.Update 'commented out as crashes code
        rs.Close
        Set rs = Nothing
        
    End If
End Sub

And for completeness here is the code called by the Main Meun's Add New Record Button's Click Event...

Private Sub Command8_Click()
   DoCmd.OpenForm "fAddRecord"
   DoCmd.GoToRecord , , acNewRec
End Sub

So you see I'[m having to open the recordset and move to the last record in order to keep the unwanted record from being added... the code seems to work...
Question 1: Any potential issues I'm missing?
Question 2: Better way?

Thanks for any help.

_{Sunday, July 5, 2015 4:22 PM}

This makes no sense. Why restricting the user that way?

The only reason to do it this way would be that you need to runs some code when the form is closed. But then you can place that code right into the On Close event. This way the user has two methods of closing the form. Which is better than restricting him.

Hi Stefan,

In a couple of unbound forms, I need to press the Save button to store the information on the form. When pressing the [X] and the information has not yet been saved, the user is asked to after all, or quit.

So there can be a reason to differentiate between the Save button and the [X].

Imb.

_{Sunday, July 5, 2015 4:23 PM}

By the time the form's Close event fires, the record has already been saved. Use the Unload event instead.

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Actually, changes are saved even before the Unload event occurs. I think the answer the OP is looking for is best handled as follows:

Option Compare Database
Option Explicit

Dim mUpdateOK As Boolean

Private Sub cmdCancel_Click()
    
    Me.Undo
    DoCmd.Close
    
End Sub

Private Sub cmdClose_Click()
    
    mUpdateOK = True
    DoCmd.Close
    
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    On Error GoTo Err_Process
    
    If (Not mUpdateOK) Then
        ' Prompt to confirm the save operation.
        If MsgBox("Changes have not been saved.  Do you want to save?", vbYesNo + vbQuestion, "Save Record") = vbNo Then
            Me.Undo
        End If
    End If
    mUpdateOK=False

Exit_Process:
    Exit Sub

Err_Process:
    MsgBox "Procedure: Form_BeforeUpdate" & vbCrLf & vbCrLf & Err.Number & " " & Err.Description, vbExclamation, "Error"
    Resume Exit_Process
    
End Sub

_{Sunday, July 5, 2015 4:41 PM}

Actually, changes are saved even before the Unload event occurs.

You and theDBGuy are absolutely right; I don't know what I was thinking. As you say, if Fran_3 wants to prevent saving the record, the BeforeUpdate event is the one to use.

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

_{Sunday, July 5, 2015 8:49 PM}

That was a better solution RunningManHD. Thanks!

_{Sunday, July 5, 2015 9:07 PM}

Glad it works for you. There is one other procedure you should add to your form module. Before doing so, open the form in design view. Then open the form's properties dialog. Set Event>Key Preview to Yes. Next, open the form's module and add the following code. This will handle the user attempting to save the record by using [Shift]+[Return].

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    
    Select Case KeyCode & Shift
    Case vbKeyReturn & 1
        mUpdateOK = True
        Exit Sub
    Case Else
        Exit Sub
    End Select
    KeyCode = 0
    Shift = 0
    
End Sub

Also, be sure to mark your other related question as answered too. Thanks and best of luck...

_{Sunday, July 5, 2015 10:45 PM}

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

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

Ken Sheridan, Stafford, England

Last updated on 2015-07-05

Share via

How to detect "X" clicked on a Form ?

Question

All replies (18)

Additional resources