Annoyed with Access prompt pop-ups encountered meanwhile performing action query? Want to suppress Microsoft Access action query warning messages but don’t know how?
Well to perform MS Access Action Query Warning Messages on/off you need to use ‘setwarning’ option. Microsoft Access setwarnings is mainly used to turn system messages on/off in Access database. these Access setwarnings can be used in various way and in different circumstances.
To know how to add setwarnings in Access 2010/2013/2016/2019 macro. OR how to perform Microsoft access setwarnings on/off. You need to just go through this post. So, let’s dive in it…..!
About MS Access Setwarnings:
By default, MS Access takes your confirmation each time before deleting up any object from the navigation pane or while running an action query. If this pop-up message annoys you to work flawlessly in your database then you can turn it OFF. Whenever you want allow appearance of such message again then turn it ON.
This task is possible with MS Access setwarnings. This option of setwarnings is mainly used to suppress Microsoft Access action query warning messages.
What’s The Need Of Suppressing Microsoft Access Action Query Warning Messages ?
At the time of executing actions queries in MS Access you may want to prevent any kind of user intervention. Like pressing OK in the warning messages while making an update, creating table, deleting or appending query.
Running such kind of Access query without switching off the system messages will annoy you with the intervention of several warning messages.
Here is the example of one such warning message encountered meanwhile executing the Make-Table query:
For disabling these Access prompt pop-ups messages, you need to make simple configuration modifications in Access options.
How To Suppress Microsoft Access Action Query Warning Messages?
Method 1: Microsoft Access VBA docmd.setwarnings
The DoCmd.SetWarnings method is used to perform SetWarnings action in VB(Visual Basic).
expression A variable that represents a DoCmd object.
|WarningsOn||Required||Variant||Use True (1) to turn on the display of system messages and False (0) for turning it off.|
Make use of the Microsoft Access VBA docmd.setwarnings for turning system messages OFF/ON.
If you turn OFF the visibility option of system messages in Visual Basic, you must turn it back on. Otherwise, it will remain OFF even if you try pressing Ctrl+Break. Or Visual Basic meets any break point.
For disabling the visibility of MS Access action query warning messages, you need to set the options to OFF in the command.
Here is the example to show how to turn off Access warning messages:
Private Sub cmdRunMakeTable_Click()
Turns off the Access warning messages
Turns the Access warning messages back on
Do such setting only if you are completely sure that your actions will not make any problems. Or unless you are completely sure about the outcomes of the actions. Otherwise don’t do it.
Method 2: SetWarnings Macro Action
SetWarnings macro action is used to turn system messages on/off in Access databases application. You can also use this action to stop message box and modal warnings which prevent the execution of macro.
Note: if the database is not trusted one then avoid performing this action.
The MS Access SetWarnings macro action includes the following argument.
|Warnings On||Specifies whether system messages are displayed. Click Yes (to turn on system messages) or No (to turn off system messages) in the Warnings On box in the Action Arguments section of the macro design window. The default is No.|
How To Use:
Access shows pop-up dialog boxes which require input like a dialog box which ask you to enter any text or choose one out of several options.
Perform this SetWarnings Macro action with Warnings On argument set to No. This will show same effect as tapping to the ENTER option whenever any warning message box appear.
Usually, OK or Yes option is chosen in the warning or message box.
After the macro finishes the execution task, Access will automatically turn on the visibility of the system messages.
You can also use this SetWarnings Macro action with the Echo macro action. As this will hide the appearance of macro results unless its execution completely gets over.
SetWarnings macro action make this simple to interact with macros. but you have to careful about turning off system messages.
Unless and until you are so sure about the outcome of any macro actions, avoid using it.
Steps To Use A Macro To Turn The Messages Off/On
From the drop-down list of macro builder choose the SetWarnings action.
If you are unable to see SetWarnings action listed, under the Design tab, in the group of Show/Hide. make a check whether the Show All Actions is also selected.
- Within the SetWarnings action, tap to the arrow present next to Warnings On. After then select the No
- Put the required macro actions for performing tasks which you want to perform with confirmation messages turned off.
- Now choose the SetWarnings After then tap to the arrow present next to the Warnings On and choose the Yes option.
- Close the Macro Builder and save the macro. At the end save the form.
Method 3: Using the RunSQL
There are several ways to run Access action query using through code or macros. In this method we will learn to execute action query in preference to RunSQL.
Using OpenQuery is similar like, double tapping the action query on the query tab present in the Database window. This also works the same but it is a little less clear what Access macro is actually doing.
While running an action query, the application pops up two dialog box messages:
A nuisance dialog:
Important details of results and errors:
The SetWarnings action of macro will suppress these dialogues messages. It suppresses both messages. So, you won’t get any idea whether the action gets completed as you have expected OR not.
Execute method gives much more effective solution if you don’t have any issue of using code instead of macro.
In the Access module, run the action query like this:
DBEngine (0)(0).Execute “Query1”, dbFailOnError
If the query executes without any dialogue message, so you don’t need to use SetWarnings. To hide the appearance of result, type the following line after the above coding line:
MsgBox DBEngine(0)(0).RecordsAffected & ” record(s) affected.”
If you get some error, after using dbFailOnError then make use of the transaction and rollback on error.
Method 4: Turn action query confirmation messages off for a database
Make use of the SetWarnings action in an AutoExec macro for turning OFF the MS Access action query warning messages.
The action won’t execute if the database having the macro is not the trusted one. to get information about how to identify the trusted database check out the article Decide whether to trust a database.
If macro named AutoExec is already present there then in that case open the macro in Design view, and then perform steps 2 to 4 of the below given steps.
- From the Create tab, choose the Macros & Code After then tap to the Macro option.
- From the drop-down list of Macro Builder choose the SetWarnings
- If you don’t get the SetWarnings action listed over here. Then go to the Design tab, present within the Show/Hide Also make to select the Show All Actions option.
- Within the SetWarnings action, tap to the arrow present next to Warnings On option and then choose the option which you want.
- Tap to the Save
- In the opened Save As dialog box, type AutoExec.
- Hit the OK option and after then close the Macro Builder. Macro will run next time when you open the database.
For more information about how to create macros, see the article: 3 Quick Ways To Create Macro In MS Access 2010/2013/2016/2019 Database.
Hopefully you have got enough idea of how to turn action query confirmation messages on or off using setwarning. So, don’t let these annoying MS Access security warnings hampering your work anymore.
Apart from this if you want to get knowledge on some other topic regarding Access database. Then ask it freely in our FAQ section.