Suppress Warnings
By Richard Rost
5 years ago
Suppress Warning Messages for Action Queries
In this video I will show you how to turn off warning messages for action queries in Microsoft Access. This will stop those annoying messages that say things like "you are about to append 1 row(s)" and "you are about to run an update query."
Rudy from San Bernardino, California (a Platinum Member) asks: I keep getting this message that says "you are about to run an append query that will modify data in your table" every time I click a button to import my daily sales leads. It's annoying. Is there any way to turn this off?
Members
I will show you how to turn the warning messages ON and OFF whenever you want with one line of VBA code and the SetWarnings command.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
 Links
Microsoft Confirmation Messages: https://tinyurl.com/yy5logcl
Update Queries: https://599cd.com/ActionQuery
Intro to VBA: https://599cd.com/IntroVBA
Intro In this video, I will show you how to suppress warning messages that appear when running update, append, make table, or delete queries (action queries) in Microsoft Access. We will look at why these warnings show up, see an example of what they look like, and then I will walk you through how to turn off action query confirmation messages in the Access settings so you can run your queries without being interrupted.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I am going to show you how to suppress warnings - how to turn off those warning messages whenever you run update, append, make table, or delete queries, otherwise known as action queries in Microsoft Access.
Today's question comes from Rudy from San Bernardino, California, a platinum member. Rudy says, "I keep getting this message that says 'You are about to run an append query that will modify data in your table' every time I click a button to import my daily sales leads. It is annoying. Is there any way to turn this off?"
Yes, of course, Rudy. There are a couple of things you can do. One is you can turn off all of the confirmation messages for all of the action queries in your database. Action queries - I have another video on action queries. Go watch that video. It is on my website and on my YouTube channel; I will put a link down below.
Basically, an action query is something that is going to change data in your database, whether it is an update or, in your case, an append, which means it is going to add records onto the end of the table. Delete queries are dangerous. They can delete all kinds of stuff, and then make table queries. So Microsoft by default warns you if it is going to do any of this stuff. You might not want that warning. I will show you how to turn it off.
OK, here I am in my TechHelp free template. You can download a copy of this template if you want to. It is on my website. There will be a link down below - go grab it if you want.
Now let us first see what the error message is that Rudy is getting. So if I have - and let us make an update query - Query Design - let us bring in the customer table. Let us just set all of the customers' states to New York. I know it is a silly query, but the point is to show you the error message.
OK, so let us make it an update query. We are going to update all of the customers to New York. OK, save it. We will just call this update1q. OK, now close that.
If I run this query - see, it is an update query - whether I run it right here from the navigation pane or I have a button that runs the query, it does not matter. OK, run the query. You get this: "You are about to run an update query that will modify data in your table. Do you want to run this?" Yeah, OK. Then it warns you again: "You are about to update six rows. Are you sure? Once you do it, you cannot undo this. You cannot reverse the changes." Say yes.
OK, now, that is a good query, and it ran. If you check your customers - let us check the customer list. Actually, let us take a look at the table - customer table. They are all New York now. OK, the query did its job. But that is annoying, especially if you do this a couple of times a day.
Rudy, for example, has a button he wants to click on and import his new leads for the day. He showed me a copy of it. He is just importing from an Excel sheet that he gets from one of his vendors. He buys leads. But every time he clicks that to import, it goes with two warning messages and then a confirmation at the end, "Are you sure?"
So how do we turn those warning messages off? There are a couple of things you can do. One is you can turn off action query confirmations for the entire database. If you make this change, it is a system-level change, so it will affect all databases on your computer.
Now, generally, if you know what you are doing, this is fine. If you consider yourself more than just an absolute novice, then feel free to make this change. The second thing you can do is turn off warning messages each time you run a query with some code. I will show that in the extended cut for the members. But for everybody else, I will just show you how to turn these messages off.
We are going to go up to File and then Options. Then we are going to go to Client Settings. Right down here, you will see Confirm - there is Record Changes, Document Deletions, and Action Queries. I am going to do another video on Record Changes and Document Deletions, but for now, Action Queries is the one we want.
I am actually going to turn off Document Deletions. That gives you a prompt every time you delete a record, and there are some pros and cons with that one. I will talk about this in a different video. But Action Queries is what we are going to turn off. I will hit OK now.
You must close and reopen the current database for this Best Fit option to take effect. Yeah, you should, but you do not really have to. Most of the time they just give you that warning message as a way to cover themselves, but 99 percent of the time, the changes will take effect immediately.
All right, so let us go back into the CustomerT and just make a few changes here. Let us put this person in Texas and we will put this person in Florida. Close it.
Now let us run that update query again. Ready? I am going to double-click. OK, I double-clicked. Nothing appeared to happen. Why? I turned off the confirmations. Open up the CustomerT again. There you go.
So it ran the action query without asking you, "Are you sure you want to run the action query" twice. Same thing for delete queries, update queries, make table queries, and all that stuff.
So there you go, Rudy. That is how you turn off the action query confirmation messages.
You want to learn more? In the extended cut for members, I show you how to suppress warnings in VBA code so you can turn them on and off whenever you want with one line of code and one command - SetWarnings. I will show you that in the extended cut for members only. As a reminder, Silver members and up get access to all of my extended cut videos.
How do you become a member? Click the Join button below the video. After you click the Join button, you will see a list of all the different types of membership levels that are available. Other members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more.
Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use.
Platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more.
But do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more.
If you liked this video, please give me a thumbs up, and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select All to receive notifications when new videos are posted.
Click on the Show More link below the video to find additional resources and links. You will see a list of other videos, additional information related to the current topic, free lessons, and lots more.
YouTube no longer sends out email notifications when new videos are posted, so if you would like to get an email every time I post a new video, click on the link to join my mailing list.
Now, if you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It is over three hours long, and you can find it on my website or on my YouTube channel.
If you like Level 1, Level 2 is just one dollar, and it is also free for all members of my YouTube channel at any level.
Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there.
Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel.
Thanks for watching this video from AccessLearningZone.com.Quiz Q1. What is an action query in Microsoft Access?
A. A query that changes data in your database, such as update, append, delete, or make-table queries
B. A query that only displays data without changing it
C. A query that only creates forms and reports
D. A query used for sorting records only
Q2. Why does Microsoft Access display warning messages when running action queries by default?
A. To notify you of speed improvements
B. To warn users that changes will be made to their data, which could be permanent
C. To limit the number of queries you can run at once
D. To require you to save your query before running it
Q3. Which of the following is NOT an example of an action query?
A. Select query
B. Append query
C. Update query
D. Delete query
Q4. How can you suppress action query confirmation messages for all databases on your computer?
A. By turning off action query confirmations in Access Options under Client Settings
B. By deleting system tables
C. By renaming your queries
D. By disabling macros
Q5. What do you have to do after turning off action query confirmations for the changes to take effect?
A. Restart your computer
B. Close and reopen the database (though often not strictly necessary)
C. Recreate your queries
D. Uninstall and reinstall Access
Q6. What risk do you take by turning off action query confirmation messages?
A. You may accidentally modify or delete data without a warning
B. Your database will become read-only
C. Queries will run slower
D. You won't be able to import data from Excel
Q7. In the video, what alternative method is shown for suppressing warnings on a case-by-case basis?
A. Using VBA code with the SetWarnings command
B. Renaming tables
C. Using only select queries
D. Logging out and back in
Q8. Where do you find the setting to confirm action queries in Access?
A. File > Options > Client Settings
B. Home > Records > Confirm
C. Database Tools > Relationships
D. View > Macros
Q9. Which membership option gives you access to the Code Vault and all sample databases from TechHelp videos?
A. Gold
B. Silver
C. Basic
D. Bronze
Q10. What is required to get access to extended cut TechHelp videos?
A. Become a Silver or higher member
B. Purchase Level 2 course
C. Download the free template
D. Post a comment on the video
Answers: 1-A; 2-B; 3-A; 4-A; 5-B; 6-A; 7-A; 8-A; 9-A; 10-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone tackles how to suppress those pesky warning messages that appear whenever you run update, append, make table, or delete queries, collectively known as action queries, in Microsoft Access.
Rudy wrote in with a question about constantly receiving the message, "You are about to run an append query that will modify data in your table," every time he clicks a button to import daily sales leads. These confirmation windows can get tiresome quickly, especially if you're running these types of queries often.
Let me start by explaining what action queries are. Whenever you run a query that changes data in your database, such as updating, adding, deleting records, or creating a new table from a query, that's considered an action query. Access intentionally warns you by default because these actions can have significant effects on your data. For example, an append query adds new records, while a delete query can remove records permanently. These warnings are safety measures, but if you're confident in what you're doing, you may prefer to turn them off.
To illustrate, I create an update query that changes the state for every customer in the table to New York. When this query is executed, Access delivers a warning that you're about to modify data. It then gives a second confirmation before the changes proceed. While these prompts are helpful for preventing accidental changes, they can interrupt your workflow if you're running reliable, routine queries.
In situations like Rudy's, where there's a simple button press to import new leads from an Excel source, having to acknowledge warning windows each time is unnecessary and irritating.
There are two main ways to suppress these warning messages. First, you can turn off action query confirmations for your entire database within Access's settings. Keep in mind that this affects all databases used on the same computer because it's a system-wide setting. If you're comfortable with the actions your queries perform and understand the risks, this option is perfectly safe.
Here's how you can do it: Open the File menu and go into Options. Then, visit the Client Settings section. Within that menu, locate the 'Confirm' options, which include Record Changes, Document Deletions, and Action Queries. For this purpose, uncheck the box for Action Queries. This removes the warning prompts for all updates, deletions, appends, and make-table queries going forward.
It's worth noting that there's another confirmation option for Document Deletions, which affects the prompts you see when you manually delete records. I'll cover the details of that particular setting in another video.
After making these changes, Access will advise you to close and reopen the database to apply the settings fully, but in most cases, the effect is immediate.
To confirm the changes, you can modify some values in your table, rerun your action query, and you'll notice that the prompts are now gone. The query will execute without any interruption, making it much more convenient for repeated operations. This approach applies to all action queries, including delete, update, and make-table varieties.
For members, in today's Extended Cut, I also explain how to use VBA code to control these confirmations programmatically. With this method, you can disable or enable warnings as needed, just for specific procedures. This technique uses a simple command in VBA, allowing you to keep confirmations active for most users but suppress them within certain automated tasks.
If you're interested in these bonus lessons, Silver members and above have access to all Extended Cut videos. Gold members enjoy downloadable databases used in these lessons and a growing collection of useful code samples. Platinum members receive all previous benefits plus access to my full library of beginner and some expert courses, not just for Access but also for other Microsoft Office applications and programming topics.
If you're just starting with Access, don't miss my free Access Level 1 course, which covers all the basics of building your first database. Level 2 is just one dollar and free to any YouTube channel member at any level.
If you have a question you'd like featured in a future video, visit my TechHelp page and submit your inquiry there.
A complete video tutorial with step-by-step instructions on everything discussed here is available on my website at the link below. Live long and prosper, my friends.Topic List What are action queries in Microsoft Access
Demonstration of Access action query warnings
Creating an update query in Access
Running update queries and handling warning messages
Turning off action query confirmation messages
Changing action query confirmation in Access Options
Navigating File and Options to Client Settings in Access
Disabling Confirm Action Queries setting
Effects of disabling action query confirmations in Access
Verifying action query execution without warnings |
