|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Turn Off Warnings By Richard Rost 
6 years ago
Turn Off Warnings in Microsoft Access How to Turn Off Warning Messages for Action Queries. Do you have command buttons that run action queries (append, delete, update, make table)? If so you know that Access gives you warning messages that can sometimes be annoying. In this Microsoft Access tutorial, I'll show you how to turn those off. Jim asks, "I’m running an Update Query from a button on a form, and every time I do I get this annoying warning message that says I’m about to update X rows. Is there any way to turn this off?" MembersI'll show you how to run an SQL statement in VBA code without making a query, how to turn the warnings OFF and ON whenever you want in VBA, how to make a MsgBox prompt "are you sure" whenever you want to, add a Shipped Date to your Order Table, and only update the ship date for orders who haven't shipped yet. 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! LinksUpdate Query: https://599cd.com/UpdateQuery
IntroIn this video, I will show you how to temporarily turn off the warning messages that appear when running action queries, like update queries, in Microsoft Access. You will learn how to adjust your Access settings so clicking a button to update your data does not prompt you with confirmation messages each time. I will walk you through creating a simple update query, adding a command button to your form, and changing the client settings to suppress those warnings for easier workflow.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, I'm going to show you how to turn off warning messages - those warning messages that you get when you run action queries in Microsoft Access. Today's question comes from Jim. Jim asks, I'm running an update query from a button on a form, and every time I do, I get this annoying warning message that says, I'm about to update X-Rows. Is there any way to turn this off? Well yes, Jim, definitely, of course there is. There's a way to do it. Pretty much everything in Microsoft Access is customizable. So here's a scenario. Here's a simple database: customers, contacts, and I've got an order table. Orders come in, they're assigned to customer ID, an order date, and I've got a has shipped box. Now the way my team works is all the orders get shipped the day, and then at the end of the day, I just want to click a button and mark them all shipped. I don't want to have to go down the line, automatically do that, and I want it to be something simple from my main menu. I just want to click a button here and mark all the orders shipped. I don't want to have my secretary digging around in menus and all that. Put a button right there. It's easy to do. First, let's throw together our update query. Create query design. Bring in the order table. That's the table we're changing. I'm going to turn this add tables box off. Change it to an update query. Now bring in the field you want to change: has shipped, and set update to true. That means we're going to update the has shipped field to true. Mark all the orders in the system as shipped at the end of the day. Save this. Control S as mark orders shipped Q. I like to add all my queries with the letter Q. If you've never done an update query before, if you have no idea what these are, I have separate videos on making update queries and explaining them in more detail. I'll put links below the video. In a nutshell, an update query, instead of viewing data a certain way, will change data for you. In this case, I'm changing all the has shipped fields to true in the order table. Alright, close this. Now again, I don't want my secretary to have to bounce through my navigation pane here and find stuff. I want to put something right on my main menu that he or she has access to. Real easy to drop a command button. Design view. Open this up. Again, command button up here. Drop it down below. We're going to go to miscellaneous, run query, pick the query. We only have one right now. Next. I like text: mark orders shipped. Next. Give the button a meaningful name: mark orders shipped. You don't have to do that, although Alex will yell at you if you don't know who Alex is. Spend some time on my website and you'll find out. And then finish. Alright, move the button in place. Make it nice and pretty. Make the form bigger. Okay, so now I have a button that when I click on it is going to run that query. And again, if you've never made a command button, if you don't know what the command button wizard is, I cover this in my classes. I'll put some links below. The purpose of this TechHelp video is to show you what happens next. Now we've got the setup done. Now let's open up my main menu. I'm going to go and click on my button. Are you ready? Click. Here's the warning message: You are about to run an update query that will modify data in your table. Are you sure you want to do this? Yeah. Another one: You are about to update nine rows. Are you sure you want to do this? Yeah. And now it's done. What did it do? If I open up my order table, you can see it marked them all as shipped. Alright, it did what it was supposed to do. But I don't want to see those two warning pop-ups. I just want to click on this button and have it do it. Alright, here's what you have to do. Go to File. Come down to Options. On the options menu, go to Client Settings. Come down a bit here to where it says Confirm. Turn off action queries. Action queries are things like update queries, delete queries, append queries, make table queries - all those things that do action on your data. They do stuff; they don't just show you stuff. I've got lessons on action queries. Alright, let's hit OK. You must close and reopen the current database. Sometimes you don't have to, it just tells you that. Let's see if it works. Click. And now it did it, and it didn't show you any warning messages. See, sometimes you do, sometimes you don't. That's just to cover my butt warning message that Microsoft puts up there so that you don't have to. Alright, I'm going to turn all of these back off again. And you can make a query to do it in the reverse too, but alright, so now I marked them all as not shipped. Alright, come in here. End of the day. Click, done. Open up your audit table and they're marked shipped again. Okay, it's that simple to turn off the warning messages. Now, there's a better way to do this, but it involves programming, some code, or at least building a macro at the minimum. This only works for your copy of the database. So if you've got multiple users on a network, for example, you're going to have to go around to all the different workstations and turn off those warning messages on each user's machine. So if you're planning on distributing this database, you're going to have to tell them to go in and change it. Plus, you might not want those error messages off all the time. If you've got sensitive stuff in there, like a delete query, for example, you might want to ask the user once in a while, are you sure you want to do this? So in the members only extended cut version of this video, I will show you how to do this in VBA code. It's not that hard, a couple lines of code. I'll show you how to turn off the warning messages only when you want to in your code, and it will work for the database as a whole. So if you've got a multi-user setup or if you're giving this database to other people to use, then the code will work for them as well. You don't have to change the setting in each database. Okay, I just finished recording the extended cut for members. I showed them how to do everything we just did without making a query and without using the command button wizard. So we dropped our own command button on the form, and then I showed in VBA how to run an SQL statement, how to turn warnings with a set warnings command on and off, which will work for everybody using the database. Now you can distribute it, you can put it on your network, and it will control just what happens for that particular case. You don't have to turn the entire system warnings on and off. I showed them how to create a message box to ask the user, are you sure, and then to show them when it's done. Then we added a ship date field, and whenever you mark the orders shipped, it will take anybody who does not have a ship date and update their ship date. So if I have some new orders come in, let's say for customer 2, and he puts them in today, that's control semicolon, by the way, same as Excel. If you want to put the current date in there. Now when I run that query, the button on the menu, I do not want to change the ship date for all the existing records. The time now is currently 12:45. So if I go out here, I hit mark order ship, it says, are you sure, that's my own custom prompt. If I say no or cancel, it does not do anything. But if I say yes, it runs the query, and then says done. If I check my table, there we go, you can see 12:45, and all these guys are left alone. So that's all covered in the members only extended cut video. How do you become a member? You click on that join button under my videos on YouTube, or if you are watching this on my website, there's a link down below the video that says become a member. Silver members and up get access to my extended cut TechHelp. You get access to live video and chat sessions, and there are lots of other perks. If you click on the join button, there you will see a list of all the different membership options. There's silver, gold, platinum, and so on. Or even if you just want to become a supporter, it's 99 cents a month, and it just tells me that you are there and that you appreciate my videos. I've got a couple of those, and I appreciate you. But do not worry, my TechHelp videos will still be free for the future. I have no plans to stop making the free TechHelp videos. If you like my videos, make sure you subscribe and ring the bell. That way you will get notifications whenever I release a new video. You can also head over to my website and check out my Access forum. If you have not yet tried my free Access Level 1 course, there's the link. Stop by, it is a three hour full tutorial on all the basics of Microsoft Access. And if you like Level 1, you can buy Level 2, which is another whole hour class for just one dollar. And that is free for my YouTube members, by the way. You do not even have to pay that dollar. If you've got questions you would like to see answered, there is my TechHelp page - drop in there. You can also email me, but I prefer you use the TechHelp page because email isn't perfect and I get so much email sometimes I do not go through it all. TechHelp goes to a special box. There are all my other cool stuff. I'll put links below - Facebook, Twitter, my blog. Stop by and take my survey. Let me know what you thought of today's video or post your comments below. Thanks once again, I hope you learned something and we will see you next time. QuizQ1. What is the main topic of this TechHelp video?A. How to create forms in Microsoft Access B. How to turn off warning messages when running action queries C. How to build a report in Access D. How to design a new table in Access Q2. What type of query was used to mark all orders as shipped in the video? A. Select query B. Append query C. Update query D. Delete query Q3. Which field was changed in the example order table to indicate orders had shipped? A. OrderDate B. CustomerID C. HasShipped D. ShipDate Q4. What is an action query in Microsoft Access? A. A query that only displays data B. A query that performs updates, deletes, or additions to data C. A query that sorts data alphabetically D. A query for importing data only Q5. Where do you go in Microsoft Access to turn off the warning messages for action queries? A. Form properties B. Macro designer C. File > Options > Client Settings > Confirm D. Data table view Q6. After changing the Confirm action query settings, what must sometimes be done for the change to take effect? A. Shut down and restart your computer B. Close and reopen the current database C. Reinstall Microsoft Access D. Delete the query and recreate it Q7. If you disable action query warnings in Access options, who will the change affect? A. All users across all computers B. Only the current copy of the database on your workstation C. Everyone in your Outlook contact list D. All databases on the computer Q8. What method was mentioned as a more robust way to toggle warnings for multi-user environments? A. Using a select query B. Modifying the Windows Registry C. Using VBA code or macros to temporarily turn warnings off D. Editing the database in Excel Q9. What is a potential risk of turning off warning messages for action queries permanently? A. Users might accidentally delete or update data without confirmation B. Access will run slower C. You cannot print reports anymore D. Forms will not open properly Q10. In the members-only extended cut, what additional feature was implemented related to marking orders as shipped? A. Added a field for tracking order status notes B. Updated existing ship dates for all records every time C. Only updated ship dates for records without an existing ship date D. Disabled all forms Q11. What is the advantage of handling warning messages with VBA code instead of just using Access settings? A. Permanent change that affects all applications B. Allows temporary suppression of warnings for specific tasks and works for all users of the database C. Makes the database run faster D. Prevents users from opening tables Q12. What suggestion is given to users who want to become members and access extended content? A. Complete a certification exam B. Click the join button on YouTube or on the website C. Email Richard Rost directly D. Buy a new copy of Access Answers: 1-B; 2-C; 3-C; 4-B; 5-C; 6-B; 7-B; 8-C; 9-A; 10-C; 11-B; 12-B 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. SummaryToday's video from Access Learning Zone covers how to turn off those warning messages you see when you run action queries in Microsoft Access. This is a common issue, and it came up today in a question from a student named Jim, who wanted to know how to stop the pop-ups that appear every time he runs an update query from a form button. By default, Access displays alerts to confirm you're about to change or update a certain number of rows whenever you run an action query. Jim wondered if there's a way to get rid of these to streamline his workflow.The answer is yes, you can turn these off, and Microsoft Access gives you options to customize your experience pretty thoroughly. Let me walk you through a typical example. Imagine a simple database with tables for customers, contacts, and orders. When orders are received, they're tagged with a customer ID, an order date, and a has shipped field. In my workflow, all the orders are usually shipped at the end of the day, and rather than marking each one individually as shipped, I want a button on my main menu to handle this with a single click. This way, a secretary or anyone else handling the database does not have to hunt through menus. It's a straightforward process. The first step is to create an update query. Using the query design tool, I add the orders table, switch the query type to update, and bring in the has shipped field. Setting its update value to true changes all orders in the table to show they've been shipped. Saving this query with a meaningful name helps keep things organized. If you are new to update queries and want more details, I have specific videos on the topic, and you can find the links to those below the video. To make things easy for everyone using the database, I also add a command button to the main menu form. The command button wizard can help you set it up so clicking the button will run the update query. I recommend giving the button a clear, descriptive name, both for your benefit and to help anyone else who might use or edit the database later. Once you've set up the button, here's where the annoyance comes in: clicking it brings up warning messages. First, Access tells you that you're about to run an update query that will modify your data. If you confirm, it follows up by telling you the number of rows you're about to change. This can get pretty old if you perform this task frequently. So here's what to do if you want to turn off those warnings. Go to File, then Options, and under Client Settings, look for the Confirm section. There's an option to turn off action query confirmations. Action queries include update, delete, append, and make-table queries, all of which modify your data. Disabling this option stops Access from showing those warning dialogs when you run those queries. Sometimes Access may suggest restarting your database, but in many cases, the setting works immediately. After disabling the confirmations, you can test the button again, and this time those warning pop-ups no longer appear. The update runs silently, marking all appropriate records as shipped, and the process is streamlined. It's important to note, however, that this setting only affects the particular copy of the database on your machine. If you have multiple users accessing the file across a network, you'll need to repeat the process on each user's computer. This might not be ideal if you're distributing the database to many people, or if you still want those warnings for sensitive operations, such as delete queries. For a more robust, professional approach, there's a better solution using VBA. In the extended cut for members, I cover how to use a simple bit of code to disable the warnings just when you need it, as part of your VBA routine. This method is safer, works for every user of the database, and is especially valuable in multi-user or distributed setups. You can include custom prompts for confirmation, display a custom completion message, and control exactly which actions trigger warnings. In the extended cut, I demonstrate how to drop your own command button onto the form, how to execute an SQL statement in VBA instead of an action query, and how to use VBA to selectively turn warnings off and on for just the duration of your operation. This method lets you distribute your solution more easily and still protect sensitive actions with your own custom messages. We also add a ship date field to the table, so whenever you mark orders as shipped, the code updates that field only if it hasn't already been set. This way, if you add new orders during the day and mark them all as shipped, only the appropriate records are updated. To access the extended cut and other membership perks, become a member on my website or on YouTube. Silver members and up receive access to extended TechHelp videos, live sessions, and more. Even basic supporters help keep these tutorials coming, and everyone is welcome to join. Remember, my regular TechHelp videos will always be free, so be sure to subscribe and turn on notifications for my channel. If you are new to Access, I highly recommend checking out my free Access Level 1 course, which is a complete introduction covering all the essentials. Level 2 is also available at a very affordable price or free for members. If you have questions you'd like answered in a TechHelp video, use my TechHelp page. I prefer questions through the web form rather than email because it helps me keep them organized. You'll find links to all my other resources, including the Access forum, blog, and social media pages, below the video. Let me know your thoughts by taking the survey or leaving a comment. For a complete video walkthrough with step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends. Topic ListCreating an action update query in AccessAdding fields to an update query Setting field values in an update query Saving and naming an update query Adding a command button to a form Using the command button wizard to run a query Assigning a query to a command button Customizing button text and names Executing an update query from a form button Understanding Access warning messages with action queries Disabling action query warning messages in Access options Locating the Confirm Action Queries setting in Access Understanding effects of turning off action query warnings Considerations for multi-user database environments |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access docmd.setwarnings update query action query runsql msgbox PermaLink Turn Off Warnings in Microsoft Access |
