473,840 Members | 1,617 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How do I show just ONE warning while running multiple delete queries

269 Contributor
I work for an electric utility and when a storm blows through, we clear out our Storm database so we can make new work assignments. To do this, we open a database and run 5 delete queries followed by 3 append queries.

I want one button to run all 5 delete queries followed by the 3 append queries. I noticed when I originally wrote the code that I got a delete query warning for ALL FIVE delete queries. I think the warning is necessary but I'd like to see it only ONCE. I was feeling frisky so I tried writing my own code to turn off the warnings. Notice how savvy I was to put the code to turn off the warnings after the first query, hoping to get one warning and then no more. (Laugh if you need to.) That didn't work--I got no error messages at all, but hey the code did run as otherwise intended so I'm still on the happy side.

I recognize the best way to do this may be to write my own warning message, ideally with a button you have to click to proceed in the deletion. I just don't know how to make that work so that if the user selects "Proceed" the code fires, and if the user clicks "Cancel" the queries do not run.

Here is the code I tried.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDeleteAll_Click()
  2. DoCmd.OpenQuery "Delete Team Selection Records"
  3. DoCmd.SetWarnings (WarningsOff)
  4. DoCmd.OpenQuery "Delete Foreman Records"
  5. DoCmd.OpenQuery "Delete Assignment Records"
  6. DoCmd.OpenQuery "Delete Equipment Records"
  7. DoCmd.OpenQuery "Delete Staging Location Records"
  8. DoCmd.OpenQuery "Append to Team Selection"
  9. DoCmd.OpenQuery "Append NonTrans to Team Selection"
  10. DoCmd.OpenQuery "Append to Staging Locations"
  11. DoCmd.SetWarnings (WarningsOn)
  12. End Sub
Can someone help me with the appropriate way to proceed in getting my ONE warning?
Apr 24 '14 #1
5 1302
28 New Member
Maybe Msgbox "Some Warning"
Apr 24 '14 #2
269 Contributor
Where should I put it in my code?
How do you get the "Accept" and "Cancel" buttons on the message box? My general experience on a MsgBox is that it has just one button, and it's "OK" or something similar. Well if they don't want to proceed, "ok" alone won't do.
Apr 24 '14 #3
28 New Member
Expand|Select|Wrap|Line Numbers
  1. IF Msgbox("Some Warning",vbInformation+vbYesNo) = vbNo then exit sub
Should put it before you start delete
Apr 24 '14 #4
Carl Witte
37 New Member
You will need to add a integer variable
Expand|Select|Wrap|Line Numbers
  1. dim responSE as integer
  2. response = msgbox("Warning",vbYesNo+vbdefaultbutton2,"WariningTitle")
  3. if response = vbno then
  4. goto Exit_supersub
  5. 'this allows me to change the type of the msgbox buttons
  6. elseif response = vbyes then
  7. 'run my queries
  8. end if
  10. Exit_supersup
  11. Exit Sub
I do weird capitalization as it helps me avoid typos.
Apr 24 '14 #5
5,501 Recognized Expert Moderator Expert

You've made such a great effort as of late... I though, maybe something a tad more complex... and as an Easter Present too (^_^)

Attached is a database in ACC2010
It uses the transaction method and the execute method on a DAO workspace/database object.

Transaction Method:
I'm working in the concept that if anthing doesn't clear out of the database, or if the all of the appends don't correctly add to the tables then nothing should happen to the current data - which is what this method allows.

Execute Method:
There are NO warnings unless you tell the method to fail on error. This is great, because you can trap for this failure and usually the last actions are undone. Better yet, you can return the number of records that the method potentially affects (or effected if you're not using the transaction method).

Nuts and Bolts:
When you open the database it will open to the form that shows the records for tbl_one. This is the table to be updated cleared etc...

On this form is a command button to open the update form.

tbl_people is the data table from where the data will be pulled and added to tbl_one and there is a form that shows whats in this table... really nothing special here... this is just testing database so I have a generic form.

There are six stored queries.

In the code:
The main code is in the comand button on the frm_ClearAndUpd ate.

-Clears the list box

-Confirms that the user does want to do the action
THis is how to handle the message box

-Starts a transaction....
.... This is alot like working with the recordset clone
all of the work is done to this copy first
if there are any errors, then you can "Rollback" all of the other changes.

- I then look at the database queries... because you cannot use a stored query directly I've looped thru the queries looking first for the deleted and then the append query types.
I then pull the name and the qry string for use in the code.

- At each point in the loop I've posted a little user feedback, as this is such a small data sample the action queries are very fast so you wont see much; however, with a large data set... these little prompts will keep the user happy.

- Finally, if there are no errors, the user is offered one more chance to back track or to commit the changes.

- If there are any errors, then the entire transaction is cancelled/rolled back, and the user is warned.

- Now I could have cleared the listbox on form close, added date times, etc... but this is just an example.

Something else... I've left a record behind in the tbl_one with each round so that you can tell what something is happening.

Simply opening the final delete query and changing the criteria and you can eliminate that too.

- ANY questions about this example let me know (^_^)
I've tried to comment the code so that you can follow what is happening; however, I've done this for quite awhile and sometimes I forget that we're not tieing shoes.
Happy Easter
Attached Files
File Type: zip BytesThread_956277.zip (196.9 KB, 57 views)
Apr 24 '14 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

by: NIck | last post by:
Hello all I try to do a multiple delete of files through FileSystemObject, but after deleting the first file, ASP gives me a "Permission denied" error. Below you see my code: Set fso = CreateObject("Scripting.FileSystemObject") For Each Item in Request.Form("filename")
by: rishi_israni | last post by:
Hi, i am having a strange problem running memory intensive queries on SQL server. I am doing an update on a table with 9 million records from another table with 50 records. the query i am running is
by: tdotsmiley | last post by:
Hi, I have multiple union queries that I have. These are from 4 different tables. Is there a way I can use the report feature so that it combines all of the queries into one file so that I can print it as one page rather than 4? thanks
by: ckpoll2 | last post by:
Hello, I am running a delete query to delete all records but those that I specify. Unfortunately, the ones that I specify are over 300 and typing "x" or "y" or "z" etc. is too large for the criteria box. Is there a better way to do this that will fit in the box? An example of the values are: E00008081 E00007975 E00006721 E00012066 E00014463
by: it2051229 | last post by:
Well i'm having a problem with the compatibility of javascript and PHP multiple delete check box.. i used a javascript for the "CHECK ALL BOXES" just like yahoo mail.. so my input is something like this "<input type='checkbox' name='checkbox' value='1'><input type='checkbox' name='checkbox' value='2'>" and the check all boxes function worked but it doesnt work with my PHP code where it is something like this : $checked = $_POST; for($i=0;...
by: pieandpeas | last post by:
I have multiple sql queries which show mostly total values (counts(*)'s etc..) based on a table of information some of the queries bring back less matching items from the table than others if i wanted to show the following, ¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦sqlquery1 sqlquery2 sqlquery3 sqlquery4 Name1 2 NULL 321 13 name2 32 1 null 41 Name3 3 1 null 4
by: Supermansteel | last post by:
My team at work uses Cognos to run multiple queries to pull in data. Then they take that data and import into Access and then usually run an Append Query to run a RND function to pull out a few samples for them to review the accounts. I was making a suggestion to my team that maybe if we only ran one Cognos query and pulled in all of the columns that everyone needs to pull samples for each of there tests that this would be better on the time...
by: atksamy | last post by:
Hi, I am running a few delete queries which r getting executed without any error. But the data in tables are not getting deleted. It is as if the queries were not run. I ran them through debugging but they are executing. Any ideas what is wrong? thanks
by: Stevestan1 | last post by:
I have a delete query I linked to in a form. Everytime I click on the link, I get two warning messages about using the delete query. The first is "Are you sure you want to run this delete query" and the second is "You are about to delete XXX number of lines." Is there any way to eliminate these warning messages? Thanks.
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.