Turn action query confirmation messages on or off

By default, Access prompts you to confirm that you want to run an action query. You can turn these confirmation messages off if you prefer not to be prompted. If the messages are not appearing and you want to be prompted, you can turn them back on.

In this article

Overview

Control action query confirmation messages for a computer

Turn action query confirmation messages off for a database

Turn action query confirmation messages off for a specific set of tasks

Use a macro to turn the messages off and back on

Overview

Action queries are a way to change the data in your database. An action query can append, delete, or update data, or make a new table using existing data. Action queries are very powerful, but that power entails some risk of unintended data changes. Because of the possibility of data loss, by default Access asks for your confirmation when you run an action query. This helps mitigate the possibility of unintended data changes.

Tip:  To see what data an action query will effect, before you run the query, on the Home tab, in the Views group, click View, and then click Datasheet View.

If you prefer that Access doesn’t ask you to confirm action queries, or if Access isn’t asking for confirmation but you want it to, you can turn the messages on or off.

  • Control action query confirmation messages for a computer   
    You can use a setting in the Access Options dialog box to set the default behavior for Access databases opened on a specific computer. If you use this setting to turn the messages off, Access will not display the messages under any circumstances. If you use this setting to turn the messages on, Access will display the messages unless they are turned off by a macro or by VBA.

  • Turn action query confirmation messages off for a database   
    Choose this method to turn warnings off for a specific database when it opens. You use the SetWarnings action inside an AutoExec macro to turn the messages off. The database file must be trusted for this macro action to work; for more information about trusted databases, see the article Decide whether to trust a database.

  • Turn action query confirmation messages off for a specific set of tasks   
    Choose this method to turn warnings off for the duration of a set of tasks and then turn them back on; for example, you might turn off the messages while a make-table query runs, and then turn them back on. You can use the SetWarnings macro action, and you can also use the DoCmd.SetWarnings VBA method. The database file must be trusted for this macro action to work; for more information about trusted databases, see the article Decide whether to trust a database.

Top of Page

Control action query confirmation messages for a computer

Important:  If you use this setting to turn action query confirmation messages off, Access will not display the messages on that computer even for a database that contains a macro or module that turns the messages on.

  1. On the File tab, click Options.

  2. On the left side of the Access Options dialog box, click Client Settings.

  3. On the right side of the Access Options dialog box, in the Editing section, under Confirm, clear the Action queries check box, and then click OK.

Top of Page

Turn action query confirmation messages off for a database

You can use the SetWarnings action in an AutoExec macro to turn action query confirmation messages off for a database.

Important:  If the database containing the macro is not trusted, the SetWarnings action won’t run. For more information about trust and databases, see the article Decide whether to trust a database.

Note:  It is possible to bypass If there is already a macro named AutoExec, open that macro in Design view, and then perform steps 2 through 4 of the following procedure.

  1. On the Create tab, in the Macros & Code group, click Macro.

    Ribbon image

  2. In the drop-down list at the top of the Macro Builder, select the SetWarnings action.

    • If you do not see the SetWarnings action listed, on the Design tab, in the Show/Hide group, make sure Show All Actions is selected.

  3. Under the SetWarnings action, click the arrow next to Warnings On and select the option that you want.

  4. Click Save.

  5. In the Save As dialog box, type AutoExec.

  6. Click OK and then close the Macro Builder. The new macro will run the next time that you open the database.

For more information about how to create macros, see the article Create a user interface (UI) macro.

Top of Page

Turn action query confirmation messages off for a specific set of tasks

There are two basic ways to turn action query confirmation messages off for a set of tasks: by using a macro, and by using a VBA module. Either way, you use code to turn the warnings off, perform the set of tasks, and then turn the warnings back on. You attach the code to an event, such as the Click Event of a command button or the Open Event of a form.

You attach a module to an event, and when the event occurs, the module runs. For example, you might attach a module to the On Open event for a form. When the form opens, the module runs. The following procedure explains how to use the DoCmd.SetWarnings method in the On Open event of a form.

  1. Open the form in Design view.

  2. If the property sheet is not visible, press F4 to display it.

  3. Click the square at the top left of the form, just below the form’s object tab.

  4. In the property sheet, on the Other tab, make sure Has Module is set to Yes.

  5. On the Event tab, click the On Open event, and then click the build button (the ellipses next to the arrow).

  6. In the Choose Builder dialog box, do one of the following:

  7. Type DoCmd.SetWarnings (WarningsOff), and then press ENTER.

  8. Enter the necessary code to perform the tasks that you want done with confirmation messages turned off.

  9. On a new line, type DoCmd.SetWarnings (WarningsOn).

  10. Close the VBA Editor, and save the form.

Use a macro to turn the messages off and back on

  1. In the drop-down list at the top of the Macro Builder, select the SetWarnings action.

    • If you do not see the SetWarnings action listed, on the Design tab, in the Show/Hide group, make sure Show All Actions is selected.

  2. Under the SetWarnings action, click the arrow next to Warnings On and select No.

  3. Enter the necessary macro actions to perform the tasks that you want done with confirmation messages turned off.

  4. Below the last task action, select the SetWarnings action, then click the arrow next to the Warnings On and select Yes.

  5. Close the Macro Builder, save the macro, and then save the form.

Top of Page

Was this information helpful?

How can we improve it?

How can we improve it?

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!