VBA Command for click a "Yes" button

Question

I have the below commands in a macro in Access

DoCmd.OpenQuery "A2- IDCD\_INBOUND\_RECORDS\_TO\_CREATE", acViewNormal, acEdit 

DoCmd.OpenQuery "A3 - DELETE\_OUTBOUND\_ORIG", acViewNormal, acEdit 

DoCmd.OpenQuery "A4 - DELETE\_OUTBOUND\_ORIG\_01", acViewNormal, acEdit 

DoCmd.OpenQuery "A5 - DELETE\_IDCD\_OUTBOUND\_ORIG\_GROUP+SUB-GROUP (Field)", acViewNormal, acEdit 

DoCmd.OpenQuery "A6 - DELETE\_OUTBOUND", acViewNormal, acEdit 

DoCmd.OpenQuery "A7 - DELETE\_IDCD\_INBOUND\_FINAL\_FROM\_PSI", acViewNormal, acEdit 

DoCmd.OpenQuery "A8 - DELETE\_IDCD\_INBOUND\_FINAL", acViewNormal, acEdit 

DoCmd.OpenQuery "A9 - DELETE\_IDCD\_INBOUND\_FINAL\_FROM\_PSI (Prep)", acViewNormal, acEdit 

Each one runs a query and it works correctly, but after each command I get a message box with yes/no option telling me I am about to run a delete query that will modify the table, do I want to continue? In all queries I want the "YES" button clicked but I cannot find the VBA equivalent to do so,

Answer 1

I tried your suggestion but I am still getting the error messages. Below is my entire VBA and one of the error messages I am getting.

Function Morning()

On Error GoTo Err_Handler

DoCmd.SetWarnings False

DoCmd.OpenQuery "A2- IDCD\_INBOUND\_RECORDS\_TO\_CREATE", acViewNormal, acEdit 

DoCmd.OpenQuery "A3 - DELETE\_OUTBOUND\_ORIG", acViewNormal, acEdit 

DoCmd.OpenQuery "A4 - DELETE\_OUTBOUND\_ORIG\_01", acViewNormal, acEdit 

DoCmd.OpenQuery "A5 - DELETE\_IDCD\_OUTBOUND\_ORIG\_GROUP+SUB-GROUP (Field)", acViewNormal, acEdit 

DoCmd.OpenQuery "A6 - DELETE\_OUTBOUND", acViewNormal, acEdit 

DoCmd.OpenQuery "A7 - DELETE\_IDCD\_INBOUND\_FINAL\_FROM\_PSI", acViewNormal, acEdit 

DoCmd.OpenQuery "A8 - DELETE\_IDCD\_INBOUND\_FINAL", acViewNormal, acEdit 

DoCmd.OpenQuery "A9 - DELETE\_IDCD\_INBOUND\_FINAL\_FROM\_PSI (Prep)", acViewNormal, acEdit 

Exit_Here:

DoCmd.SetWarnings True 

Exit Sub 

Err_Handler:

MsgBox Err.Description, vbExclamation, "Error" 

Resume Exit\_Here 

End Function

Answer 2

Do I just need this at the begining of my macro or after each query command?

You need to turn warnings off before executing the queries, then on again afterwards.  You should also ensure they are turned on again in the event of an error:

On Error GoTo Err_Handler

    DoCmd.SetWarnings False

    DoCmd.OpenQuery "A2- IDCD_INBOUND_RECORDS_TO_CREATE"

    DoCmd.OpenQuery "A3 - DELETE_OUTBOUND_ORIG"

    DoCmd.OpenQuery "A4 - DELETE_OUTBOUND_ORIG_01"

    DoCmd.OpenQuery "A5 - DELETE_IDCD_OUTBOUND_ORIG_GROUP+SUB-GROUP (Field)"

    DoCmd.OpenQuery "A6 - DELETE_OUTBOUND"

    DoCmd.OpenQuery "A7 - DELETE_IDCD_INBOUND_FINAL_FROM_PSI"

    DoCmd.OpenQuery "A8 - DELETE_IDCD_INBOUND_FINAL"

    DoCmd.OpenQuery "A9 - DELETE_IDCD_INBOUND_FINAL_FROM_PSI (Prep)"

Exit_Here:

    DoCmd.SetWarnings True

    Exit Sub

Err_Handler:

    MsgBox Err.Description,vbExclamation, "Error"

    Resume Exit_Here

Note that the above is VBA code for the button's Click event procedure, not a macro.

Answer 3

Don't use a macro. Use VBA. You can then wrap the OpenQuery methods with the SetWarnings methods.

Answer 4

Thank you.

Do I just need this at the begining of my macro or after each query command?

James

Answer 5

Hi,

Instead of clicking Yes you have to avoid the msg box:

DoCmd.SetWarnings False

'here all your DoCmd.OpenQuery lines
DoCmd.SetWarnings True

Servus
Karl
****************
Access Forever News DevCon
Access-Entwickler-Konferenz AEK