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

[DanicaDear]

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.

Private Sub cmdDeleteAll_Click()
DoCmd.OpenQuery "Delete Team Selection Records"
DoCmd.SetWarnings (WarningsOff)
DoCmd.OpenQuery "Delete Foreman Records"
DoCmd.OpenQuery "Delete Assignment Records"
DoCmd.OpenQuery "Delete Equipment Records"
DoCmd.OpenQuery "Delete Staging Location Records"
DoCmd.OpenQuery "Append to Team Selection"
DoCmd.OpenQuery "Append NonTrans to Team Selection"
DoCmd.OpenQuery "Append to Staging Locations"
DoCmd.SetWarnings (WarningsOn)
End Sub
 

Can someone help me with the appropriate way to proceed in getting my ONE warning?

[anvidc]

Maybe Msgbox "Some Warning"

[DanicaDear]

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.

[anvidc]

IF Msgbox("Some Warning",vbInformation+vbYesNo) = vbNo then exit sub

Should put it before you start delete

[Carl Witte]

You will need to add a integer variable

dim responSE as integer
response = msgbox("Warning",vbYesNo+vbdefaultbutton2,"WariningTitle")
if response = vbno then
goto Exit_supersub
'this allows me to change the type of the msgbox buttons
elseif response = vbyes then
'run my queries
end if
 
Exit_supersup
Exit Sub
 

I do weird capitalization as it helps me avoid typos.

[zmbd]

DanicaDear:

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_ClearAndUpdate.

-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