How to cancel a form close in Close Event?

Question

I'm sure this is very simple, but I can't find it. In the close event of an Access Form, how can I cancel closing the form? I have a test that counts the records in a table. If that table has records, I want to ask the user if they want to close or go back and work with them. So how do I cancel the close event?

Tellez

Answer 1

You can use the Unload event:

GlobalVar ButtonClicked

Private Sub Form_Open(Cancel As Integer)
     ButtonClicked = False
End Sub

Private ClickMe_Click(Cancel As Integer)
     ButtonClicked = True
End Sub

Private Sub Form_Unload(Cancel As Integer)
     If Not ButtonClicked Then
         Cancel = True
     End if
End Sub  

Order of events for database objects

Gand

I'm not sure I understand, should I pass something to the Form_Unload event in the close event? – Tellez

Oh, I see, put the test in Form_unload then set Cancel = True. msdn.microsoft.com/en-us/library/aa211464%28v=office.11%29.aspx – Tellez

I have added a lot more notes on how to use it. – Gand

Its the Cancel As Integer that really confused me. I would expect a boolean there. But I guess VBA translates TRUE and FALSE to 1 and 0 automtically? – Tellez

This makes the form blink before showing my msgbox in Unload. And when dismissing the msgbox, I get a runtime error, "No current record.". Normally you have records on your forms. ;) So do you have an idea how to solve this? – Gascony

Answer 2

Study and try this code, it worked for me. Replace necessary variable names with your chosen names. Paste the code in the form_unload Event of your form. WARNING!!!: After you perform this operation you will find it difficult to access your form in design and layout view

    Private Sub Form_Unload(Cancel As Integer)
        userresponse = MsgBox("Are you sure you want close? All your work wouldn't be saved", vbYesNo, "Database Information")
        Select Case userresponse
        Case 6
            Cancel = False
            'this line opens another form in my own case
            DoCmd.OpenForm "EngMenu"  

        Case 7
            Cancel = True
            'this line keeps my own form open in my own case
            DoCmd.OpenForm "UpdateForm"


        Case Else:

            MsgBox "You are not allowed to perform this operation", vbInformation, "Database Information"
        End Select
    End Subenter code here

Bissextile

Answer 3

Use the "Form_BeforeUpdate(cancel as integer)" event and set cancel to True.

Notice that you simply will not be able to close at all unless you add some logic to actually allow updating the database.

Trexler