change the action of an existing button

[hqengint]

Here is what I want to do: Be able to change the action of a particular button. I know that I can do this with the 'OnAction' method. In fact, I have changed what happens when the user selects 'Close' from the 'File' menu. However, since very few people would ever close a window that way, I would like to be able to change what happens when the user pushes the Close Button [X]. The problem is that I do not know how to reference the button.

Here is the code that I have for the Close command in the file menu:

Set closecommand = CommandBars("File").Controls("Close")
closecommand.OnAction = "newCloseAction"

Sub newCloseAction()
MsgBox "This works"
End Sub

 

[ghudson]

If you are talking about closing a form, then you need to put your code in the forms OnClose event.

HTH

 

[hqengint]

Close button

The problem is that it is not a form that I want to close, but the datasheet view of a query.

 

[ghudson]

Can you use this to close the open query?

DoCmd.Close acQuery, "YourQueryNameHere", acSaveNo

HTH

 

[hqengint]

I need to wait for the user to close the window

 

[hqengint]

For anyone that might have a similar problem: I was able to get around changing the close event for the datasheet view of the query by automatically creating a datasheet form, and then using the onclose event. My code follows.

DoCmd.OpenQuery "QueryName"
DoCmd.RunCommand acCmdNewObjectAutoForm
DoCmd.Close acQuery, "QueryName"
DoCmd.RunCommand acCmdDatasheetView
'The following is so that the user is not prompted to save the form
DoCmd.Save acForm, "Form1"
Forms("Form1").OnClose = "=DetermineIfSave()"

Function DetermineIfSave()
SaveQuery = MsgBox("Would you like to save this query?", vbYesNo)
If SaveQuery = vbYes Then
queryname = InputBox("Enter the name you would like for the query:", , "DefaultName")
'The following If...Then statement is incase the user pushes 'Cancel'
If queryname = "" Then
DoCmd.DeleteObject acQuery, "QueryName"
DoCmd.OpenForm "DeleteForm"
Exit Function
End If
DoCmd.Rename queryname, acQuery, "newQuery"
Else: DoCmd.DeleteObject acQuery, "newQuery"
End If
DoCmd.OpenForm "DeleteForm"
End Function

The form 'DeleteForm' was to delete the form that was automaticall created. If you were to try and put: 'DoCmd.DeleteObject acForm, "Form1"' into the OnClose Event then you would get an error message that says you can't delete a form while it is open. Therefore, I created another form with a command button the user must push that would delete the form.