Custom Search

Disable Access Prompt when a record is changed, table deleted, or action queries run

Suppose you have a long process in Access that is triggered by clicking a form button. This process does a lot of things behind the scene, such as updating records, deleting and recreating tables, or emptying tables. By default, Access prompts for confirmation when each of the following three actions takes place.

1. Record changes
2. Document deletions such as deleting a table or report
3. Action queries such as insert, update, or delete query.

Because the process is long and complex, it quickly becomes annoying and unnecessary that you have to confirm the action each time Access pops up a confirmation dialogue box.

To disable all these Access prompt pop-ups, it's a simple configuration change in Access options and majority of these pop-up dialogue boxes should be gone forever.

For Access 97, 2000, XP, 2003

1. Go to Tools -> Options on the menu bar.

2. Click the Edit/Find tab and uncheck the three checkboxes in Confirm section. See below.

3. Click OK to close the Options windows.

For Access 2007

1. Click the Office Button at the top left corner of Access window.

2. Click Access Options button.

3. Click Advanced tab in the left hand side navigation menu and uncheck the three checkboxes in Confirm section. See below.

4. Click OK to close all the windows.

Now run your process again in Access, you will be pleased you'd not be prompted as to whether or not you want to take the action. Most of the prompts are gone.

However, sometimes you may come across odd occasions where you unselected the three checkboxes, but you still get prompts. The cause could be in either the VBA code or behind the code for Form event properties (e.g. BeforeDelConfirm Event property) in your Access database.

In VBA, the DoCmd.SetWarnings method can be used to turn system messages on or off.

Please note that, if you turn the display of system messages off in Visual Basic, you must turn it back on, or it will remain off. That is, you must use DoCmd.SetWarnings method in pairs. It's something like below. You turn it off, do something, then turn it back on.

DoCmd.SetWarnings False

' Do something here
strSQL = "DELETE * FROM customers"
DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

The effect of the above code may also vary between versions of Access. It may work in Access 2000, but certain actions may not work in Access 2003 or other versions. You need to test the code on individual basis.

Happy Unpromting!


Other Recent Articles from the MS Access category:

1.Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
2.How to easily get a list of field names in MS Access
3.How to count distinct records in MS Access
4.How to do transaction based processing in MS Access
5.How to open a document (local/network file or web page) from MS Access
6.How to use ADOX to create unique composite index - the VBA approach
7.How to do cross-table update queries in MS Access - the right way
8.Three efficient ways to get the number of records by using VBA
9.How to create a composite unique index (not as a primary key) in MS Access
10.Use VBA to get the correct number of records in a Recordset object
11.How to hide and unhide a MS Access object
12.How to return multiple values from a VBA function (Part 3)
13.How to return multiple values from a VBA function (Part 2)
14.How to return multiple values from a VBA function (Part 1)
15.Three ways to programmatically duplicate a table in MS Access by VBA
16.Create a DLL by CSharp or VB.Net for VBA
17.How to correctly reference and call a DLL
18.How to register a C# or VB.Net DLL
19.Email address validation by Regular Expressions using VBA
20.Fix MS Access error: Query must have at least one destination field
21.How to unselect radio buttons in MS Access after it has been selected
22.How to Change Query Timeout Value for MS Access SQL Queries
23.What is Northwind Traders database

Copyright © 2017 All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

Home | Feedback | Terms of Use | Privacy Policy