Prevent "Action Failed" Dialog box

[zerox39]

Hi.


I have a converted macro which asks the user a vbayesno.
The code works perfect when "Yes" is selected, but when you select "No" it shows the "action failed" dialog box.
How can i prevent the dialog box from opening?

Here's my macro code

Function Control_Panel_Send_Submission_Confirmations()
On Error GoTo Control_Panel_Send_Submission_Confirmations_Err

    If MsgBox("Confirm this program?", vbYesNo) = vbNo Then End
    DoCmd.SetWarnings False
    Call send_program_confirms
    Beep
    MsgBox "Promo Confirms Sent", vbInformation, "Promo Confirms"


Control_Panel_Send_Submission_Confirmations_Exit:
    Exit Function

Control_Panel_Send_Submission_Confirmations_Err:
    MsgBox Error$
    Resume Control_Panel_Send_Submission_Confirmations_Exit

End Function

[zmbd]

Change line 4 to read:

If MsgBox("Confirm this program?", vbYesNo) = vbNo Then Exit Function

Although this is not the prefered way to exit a function.

It also doesn't appear that your code is a function.
Functions return values to the calling code
Subs are for actions
Thus everywhere there is the word function you should change that to read "SUB"

[zerox39]

Thanks zmbd.

I tried this and i still got the box when "No" is selected.
[IMGnothumb]http://i47.tinypic.com/1y7sbt.jpg[/IMGnothumb]

[Z{MOD- Text from image for those that can't see it}

Title: Action Failed
Macro Name: Control Panel: Command 54 : OnClick : Embedded Macro
Condition: True
Action Name: RunCode
Arguments: Control_Panel_S end_Submission_ Confirmations()
Error Number: 2950

]

I tried this with 2 variations of the code. the first using the exact wording "then exit function" and the second "then exit sub" on line 4

Sub Control_Panel_Send_Submission_Confirmations1()
On Error GoTo Control_Panel_Send_Submission_Confirmations1_Err

    If MsgBox("Confirm this program?", vbYesNo) = vbNo Then Exit Function
    DoCmd.SetWarnings False
    Call send_program_confirms
    Beep
    MsgBox "Promo Confirms Sent", vbInformation, "Promo Confirms"


Control_Panel_Send_Submission_Confirmations1_Exit:
    Exit Sub

Control_Panel_Send_Submission_Confirmations1_Err:
    MsgBox Error$
    Resume Control_Panel_Send_Submission_Confirmations1_Exit

End Sub

and

Sub Control_Panel_Send_Submission_Confirmations1()
On Error GoTo Control_Panel_Send_Submission_Confirmations1_Err

    If MsgBox("Confirm this program?", vbYesNo) = vbNo Then Exit Sub
    DoCmd.SetWarnings False
    Call send_program_confirms
    Beep
    MsgBox "Promo Confirms Sent", vbInformation, "Promo Confirms"


Control_Panel_Send_Submission_Confirmations1_Exit:
    Exit Sub

Control_Panel_Send_Submission_Confirmations1_Err:
    MsgBox Error$
    Resume Control_Panel_Send_Submission_Confirmations1_Exit

End Sub

[Rabbit]

Wait, is this a function or a sub? Your first post shows it as a function. But your later post shows it as a sub. It makes a difference.

Also, it would have helped to know that this code is run from a macro. When you said it was converted from a macro, I was under the impression that the macro was no longer used.

It would also have been helpful to know that the error code was 2950 along with the error message that you posted.

Are you using Access 2007? This is what I found from the Microsoft website when I googled the error code:

This issue occurs if the database is not trusted by Access 2007. By default, Access 2007 opens databases that are not trusted in Disabled mode. In Disabled mode, executable content is disabled.

[NeoPa]

It seems you've managed to get the impression that the error mesasage comes from this code. I believe the error message comes from the fact that an action has been requested but was not allowed to execute (See Rabbit's detailed comment on this). Can you state certainly that the error is a code error?

[zmbd]

Late evening... complex answer
assuming that the code is being ran from a button on a form?
mixing macros and vba will do this to you
try to answer in the morning.

[zerox39]

Hi Rabbit. This code is being run by a button on my form.
I am using access 2007, but I do have the database in trusted location along with being in "enabled mode".

[zerox39]

Hey NeoPa, I do not think the error box is a code error, I understand that this is a standard function of the software when a button or regular macro is interrupted. I'm more hoping that it can be suppressed.
Thanks

[zmbd]

Zerox39:
What is happening is that the OnClick event of your button has an Embedded macro. That macro is then calling the VBA code. This happens since V2007 as it is Microsoft's way of dealing with the fact that Sharepoint doesn't know what to do with VBA.
When the VBA suddenly stops, It bails out to the macro; however, without setting any error conditions so the macro doesn't know what to do and pukes. Very bad of MS to do that to you! They should have to suffer 10,000 lashings from a talkshow host and a bath in camel dung!

There are afew ways to fix this. The easiest is to get rid of the calling macro and take the called code directly to the control.

To do this:

Not that I am expecting any issues what so ever, as I've done this hundreds of times....

Before we start - make a copy of the form or the entire database.
BACKUP! BACKUP! BACKUP! BACKUP! BACKUP! BACKUP! BACKUP!
If somthing goes wrong here, I don't want you in a deeper hole than when we started!

:)


OK - now that we have an escape plan in place:
Press <Alt><F11>
This will open the Visual Basic Editor, "VBE" window
On the menu:
{Tools}/{Options}
In the "Options" dialog that opens:
Select the "Editor" tab.
Uncheck "Auto Syntax Check" - all this does is pop up a message box with an "OK" button to tell you that you messed up the code. The line with the error will already turn red so this is un-needed.
Check mark everything else on this tab.
The remaining tabs are OK with the default values.
[OK]
Close the VBE if you want... we'll get back to it in few moments. ;)

Go back to the Access program.
Open the form with your problem child in Design mode.
Select the control that is causing this issue (appears to be named "Command 54" - this is another clue that the Wizard is at play).
Right Click on the control and select "Properties " - it should be the very last entry in the popup menu... not "Form Properties" which is above it.
You will now get a box that opens with alot of stuff,
At the top of this box select the tab titled "Event"
In the rows below that show up will be one with "On Click" in the left column and in the right column will be "[Embedded Macro]"
Next to that will be two buttons, a down-arrow and "[...]"
Click on the down arrow.
You should only have one option "[Event Procedure]" select that option.
Now click on the "[...]" button. The VBE window will open and you will see a new entry:

Private Sub Command_54_Click()

End Sub

You will cut and paste the code from line 2 to line 17 from the second of code blocks you posted (the first has a syntax error) in between the two lines in the VBE. We're omitting the very first and very end lines of the code you posted.

so now you will have somethin like

Private Sub Command_54_Click()

On Error GoTo Control_Panel_Send_Submission_Confirmations1_Err 
  
    If MsgBox("Confirm this program?", vbYesNo) = vbNo Then Exit Sub 
  
[Z{omitted the remaining code....}]

End Sub

Save!

Now From the menu, Debug, Compile.
Hopefully you will not have any errors occure at the is point.
If not... you can close the VBE
Return to the Access program
Save and then Close the form
Re-open the form
click on what was the problem child.
-
Hopefully, all should be well!

[zerox39]

Thanks ZMBD!
Worked perfect. You could tell I was new at this.
I now see the difference of changing a button from a macro to VBA code.

Thanks so much