Code not assigned to form

[Don]

I want to run a delete query if someone clicks the x in the upper right-hand
corner of the Access application instead of clicking the quit button on my
form. I'm sure this is very simple, but I'm not sure how to do it.

Can someone show me the proper syntax? Also, what module should this code
be written to?

 

[Clifford Bass]

Hi Don,

How about putting it in the form's On Close or On Unload event? Those
are both run when Access is closed.

Clifford Bass

 

[Jim Burke in Novi]

One thing Clifford left out - in order to know whether they clicked the
form's quit button, you'd need a module-level variable in your form module,
say it's called closeButtonClicked. In your form's open event, set this to
False. In the OnCLick event of the quit button, before closing the app, set
closeButtonClicked = True. then, in your unload or close event, run the query
if closeButtonClicked = false

 

[Don]

I don't mean to be ignorant--but I am. Can you be a little more elementary
about the proper syntax to write this code? I've not done this before and
I'm not sure how to write this. My command button is Command5. My form is
frmMenu.

Thanks

 

[Clifford Bass]

Hi Don,

Just a short aside. I would recommend that you name your various
controls something more meaningful than the generic, Accesss-supplied
Command5, Text1, etc. That makes it much easier to follow in code or
elsewhere what control is being used. So I might use cmdClose for the name
of the close button.

Anyway, your code might look something like this, which incorporates
Jim's thoughts about whether or not the close button was clicked. I think my
preference would be to use the On Unload event because you can cancel the
unload in the event of an error. Of course, if clicking on the close button
just runs the same code, you probably would want to remove it from that event
so it is only in the unload event. And in which case you would not need to
check to see if the close button was clicked.

================================================

Private m_boolCloseButtonClicked As Boolean

Private Sub cmdClose_Click()

m_boolCloseButtonClicked = True
DoCmd.Close acForm, Me.Name, acSaveNo

End Sub

Private Sub Form_Open(Cancel As Integer)

m_boolCloseButtonClicked = False

End Sub

Private Sub Form_Unload(Cancel As Integer)

On Error GoTo Handle_Error

If Not m_boolCloseButtonClicked Then
DoCmd.RunSQL "some SQL statement"
End If

Exit_Sub:
Exit Sub

Handle_Error:
Cancel = (MsgBox(Err.Number & ": " & Err.Description & vbCrLf & vbCrLf & _
"Do you wish to ignore and close anyway?", vbCritical + vbYesNo +
vbDefaultButton2) _
= vbNo)
Resume Exit_Sub

End Sub

================================================

Hope that helps,

Clifford Bass