no warning messge when runing the delete, append or update query!

[atiq]

why isn't my Access giving me warning before runing the delete, append or update query because usually it warns you that you are about to append, update or delete the following number of records. It must be the settings, can someone help!

 

[rainman89]

why isn't my Access giving me warning before runing the delete, append or update query because usually it warns you that you are about to append, update or delete the following number of records. It must be the settings, can someone help!

Tools>options> edit/find tab
Make sure confirm
action queries is checked

 

[boblarson]

And, if you've EVER used

DoCmd.SetWarnings False

Then you could have messed things up because if you do that and it errors out before getting to DoCmd.SetWarnings True, then you will not get anymore warnings, ever - until you turn them back on.

So, what I usually do is to put, as the first line, in my error handler -
DoCmd.SetWarnings True

And if you want to see if that was the problem, copy this into a Standard Module (not a form module)

Public Sub TurnWarningsOn()
  DoCmd.SetWarnings True
End Sub

and then click the run button. That will turn them back on for you.

 

[aftershokk]

I tried both both replies it is not working for me? any other ideas

 

[aftershokk]

I forget to post that it did work.

 

[Dudley]

Does this override Confirm Action Queries?

And, if you've EVER used

DoCmd.SetWarnings False

Then you could have messed things up because if you do that and it errors out before getting to DoCmd.SetWarnings True, then you will not get anymore warnings, ever - until you turn them back on.

So, what I usually do is to put, as the first line, in my error handler -
DoCmd.SetWarnings True

And if you want to see if that was the problem, copy this into a Standard Module (not a form module)

Public Sub TurnWarningsOn()
  DoCmd.SetWarnings True
End Sub

and then click the run button. That will turn them back on for you.

Hi, does DoCmd.SetWarnings True override the system setting Confirm Action Queries 'checked'? It doesn't seem to on my machine - Confirm Action Queries is "on" right now, and I have code to set warnings false before running some append and update queries, and I'm being asked if it is okay to perform the queries. I must be missing something. Here's some code:

Private Sub cmdImport1_5_Click()
    Dim db As Database
    
    Set db = CurrentDb()
    If MsgBox("Is this the correct file?", vbQuestion + vbYesNo, "File verification") = vbYes Then
        DoCmd.SetWarnings False
'        CleanUpTables   'Delete previously-imported tables - tables whose names end in "1"
        'Import the new objects.  strPath is the file you selected from the browsing window. This is the file you import from.
        DoCmd.TransferDatabase acImport, "Microsoft Access", Me.Text1, acTable, "tblClients", "tblData_Clients1", False
        DoCmd.TransferDatabase acImport, "Microsoft Access", Me.Text1, acTable, "tblClientMedicalConditions", "tblData_ClientMedicalConditions1", False
        DoCmd.TransferDatabase acImport, "Microsoft Access", Me.Text1, acTable, "tblLSPData", "tblData_LSP_Scores1", False
        DoCmd.TransferDatabase acImport, "Microsoft Access", Me.Text1, acTable, "tblLU-Agency", "tblLU_Agency1", False
        DoCmd.TransferDatabase acImport, "Microsoft Access", Me.Text1, acTable, "tblLU-Ethnicity", "tblLU_Ethnicity1", False
        DoCmd.TransferDatabase acImport, "Microsoft Access", Me.Text1, acTable, "tblLU-MedicalCondition", "tblLU_MedicalCondition1", False
        DoCmd.TransferDatabase acImport, "Microsoft Access", Me.Text1, acTable, "tblLU-Program", "tblLU_Program1", False
        
'        If MsgBox("Do you really want to proceed with the import?", vbQuestion + vbYesNo, "Proceed?") = vbYes Then
        'Run action queries to perform the data transfer
        DoCmd.SetWarnings False
        db.Execute "qryMIGRATEv5_1a_Agency"
        db.Execute "qryMIGRATEv5_1b_Ethnicity"
        db.Execute "qryMIGRATEv5_1c_Program"
        db.Execute "qryMIGRATEv5_1d_MedicalCondition"
        db.Execute "qryMIGRATEv5_2a_ClientsData"
        db.Execute "qryMIGRATEv5_2b_ClientMedicalConditions"
        db.Execute "qryMIGRATEv5_3_LSPDataNewProgramNewAgencyNewClientID"
        DoCmd.SetWarnings True
        
        ProcessImportedData
        
    Else
        MsgBox "Import canceled." & vbCrLf & "Please select correct file.", vbInformation, "System Notice"
    End If
    DoCmd.SetWarnings True
End Sub

Thanks for your help!
Thanks!

 

[evanscamman]

Same Problem!

I'm having the same problem. I did everything suggested in this thread, but still no confirmation when deleting a record.
Any other ideas?

Thank you,
Evan

 

[evanscamman]

Problem Solved!

I think it is fixed now - but I don't know why.
After reading someone else's post, i tried messing around with the
Delete, BeforeDeleteConfirm, AfterDeleteConfirm events.
They didn't already exist so I created them. Even though they were blank events, suddenly my warnings messages started reappearing!
Looks like it just needed some kind of "reboot" of the delete process.

Evan

 

[aftershokk]

warning

I just make sure I set warnings "on" now consistently after I turn them off in my macros. My issue is resolved.

 

[evanscamman]

Problem UN-solved

I spoke too soon. The problem comes and goes, and it seems to be SOOO weird, I wonder if anybody can figure this out...?
Access is supposed to run the delete events in this order:

BeforeDeleteConfirm
AfterDeleteConfirm
Delete

But it is skipping the 1st 2 events and going directly to Delete

Here's the weird part:
If I add a break-point somewhere in the delete event (but don't change any code), the next time I delete a record there is no confirmation - but the second time I delete, suddenly the Before and After delet confirm events are triggered!!! My delete confirmation is back.
And it stays back as long as I still have a break point in the Delete event.
As soon as i remove the break point, (actually the time after that), Access starts skipping the Before and After events again!

This is too weird for me - does anyone have any idea of what is happening here? Nothing in the code is changing - i'm just toggling a breakpoint!

Thank you,
Evan

 

[WhiteBearMike]

Problem UN-solved

I spoke too soon. The problem comes and goes, and it seems to be SOOO weird, I wonder if anybody can figure this out...?
Access is supposed to run the delete events in this order:

BeforeDeleteConfirm
AfterDeleteConfirm
Delete

But it is skipping the 1st 2 events and going directly to Delete

Here's the weird part:
If I add a break-point somewhere in the delete event (but don't change any code), the next time I delete a record there is no confirmation - but the second time I delete, suddenly the Before and After delet confirm events are triggered!!! My delete confirmation is back.
And it stays back as long as I still have a break point in the Delete event.
As soon as i remove the break point, (actually the time after that), Access starts skipping the Before and After events again!

This is too weird for me - does anyone have any idea of what is happening here? Nothing in the code is changing - i'm just toggling a breakpoint!

Thank you,
Evan

Hi Evan, This is an old issue but I am experiencing the exact same issue now running Access 365. I cannot get the confirm delete message to show unless I do what you do above. Very flakey. I have warnings completely turned on as well. I wonder if anyone every found a way around this Access bug.

 

[Isaac]

Just turn them back on in the immediate window and they will be back.
docmd.setwarnings true [press enter]

and check your access options > client settings > confirm.

 

[GinaWhipp]

Hi Evan, This is an old issue but I am experiencing the exact same issue now running Access 365. I cannot get the confirm delete message to show unless I do what you do above. Very flakey. I have warnings completely turned on as well. I wonder if anyone every found a way around this Access bug.

You need to make sure every place you turned them off that you turn them back on or it will continue to happen.

 

[The_Doc_Man]

Using DoCmd.RunSQL sqlstring, you get warnings consistent with the SetWarnings flag TRUE or FALSE. If you have an error OR if a warning is going to be needed, you will get it.

Using some variant of CurrentDB.Execute sqlstring, you do not get warnings because they get to the underlying database engine via a different mechanism (pathway) than DoCmd.RunSQL. In particular, the query gets to the ACE (or JET) engine more directly.

The .Execute method will only signal an error if you do this:

CurrentDB.Execute sqlstring, dbFailOnError

- which means you also need to have error trapping enabled if you DID want error messages. But .Execute does not care about warnings. On the other hand, if you use the dbFailOnError option, then if the .Execute fails the query does an automatic rollback.

 

[WhiteBearMike]

Thanks Gina and Doc. The program is huge and complicated and I finally figured out that those settings were being turned off via VBA code in an obscure place. So I had to add the VBA code to turn them back on. In addition I had to made sure the docmd.setwarnings was set to true. Here are the vba codes (change the 1 to 0 to turn them off):

Application.SetOption "Confirm Action Queries", 1
Application.SetOption "Confirm Document Deletions", 1
Application.SetOption "Confirm Record Changes", 1

 

[isladogs]

These are boolean options. Suggest you use either -1 or True (and 0 or False)

 

[Pat Hartman]

In order to avoid this issue, I use a macro to set warnings on/off and in the macro, I set the hourglass ON when I set warnings off and vice versa. These are the ONLY two macros I ever use. Setting the hourglass on is an annoying "note to self" that the warnings are of. You may not have found all the instances so I would advise you to make the macros and then using find and replace, substitute the macros for the existing commands. This won't find any missing reset commands BUT, you will be automatically aware that something is missing if the hourglass is on. Warn your users also.

NOTE: leaving warnings off accidentally can cause you to loose object changes you make during development. If warnings are off and you change an object and just close it without specifically saving it, Access will just discard the changes without warning. Very painful

 

[Isaac]

NOTE: leaving warnings off accidentally can cause you to loose object changes you make during development. If warnings are off and you change an object and just close it without specifically saving it, Access will just discard the changes without warning. Very painful

That is a great reminder and something I often forget. I usually just use .execute and never end up with any reason to adjust warnings in the first place, but a good reminder if they are off, esp. for those of us in the habit of saving things (in general) but x'ing out of them and hitting the space bar!

 

[Pat Hartman]

I also tend to use the .execute method also but not always. The macro solution came about after I lost hours of work two days in a row during my formative years. Of course by the third time I wrote the code, it was actually more concise