Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 866
  • Last Modified:

No confirm after delete

I have a delete button on my forms that uses the folowing code:

Application.RunCommand acCmdDeleteRecord

Whenever I used it before it would always ask me to confirm the delete ("You are about to delete one record... are you sure?...) something to that effect. Also if I tried to close a form without filling in the mandatory fields it would tell me that I could not save the record because mandatory fields were missing.

Recently I have noticed that the same delete button with the same code no longer asks me to confirm the delete, but instead deletes the record straight away. Also if I don't fill in all the mandatory fields, the form closes straight away and does not save the incomplete record.

I don't know why it is suddenly behaving like this; I do not recall making any changes. Please can someone tell me why this is happening?

(The Edit/Find Options in Tools has all three options checked for confirm (Record changes, Document deletions, and Action queries)).
0
Annu
Asked:
Annu
  • 8
  • 5
  • 3
  • +6
1 Solution
 
DennisBorgCommented:
Have you issued the following statement?


   DoCmd.SetWarnings False


If you want to temporarily turn the warnings back on, you can always use the following code:

   DoCmd.SetWarnings True
   DoCmd.RunCommand acCmdDeleteRecord
   DoCmd.SetWarnings False


-Dennis Borg
0
 
dovholukCommented:
it seems (as DennisBorg has pointed out) that you have used the docmd.setwarnings false command.  this is a very common pitfall for many people. i myself used to fall into this category when i used docmd.runsql to run my sql. then i found the currentdb.execute method instead. it does NOT ask you if it's ok to delete/insert/update any data, it will simply do what it's told and not raise any issues.

if you are using the docmd.setwarnings false, docmd.runsql, docmd.setwarnings true sequence, this can all be replaced by simply using currentdb.execute.

for example this three line statement:

docmd.setwarnings false
docmd.runsql "INSERT INTO TABLE1 (field1) VALUES ('value')"
docmd.setwarnings true

can be replaced with

currentdb.execute "INSERT INTO TABLE1 (field1) VALUES ('value')"

the internal access warnings will never be turned off, so you never need worry about having those messages suppressed when they "SHOULD" occur.

just adding to what dennisborg has already written..

dovholuk

now
0
 
BondoCommented:
Excellent comment dovholuk
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
DraakCommented:
has the vba code behind the button been altered. Maybe someone has added the following code:

Application.SetOption "Confirm Record Changes", False
Application.SetOption "Confirm Document Deletions", False

It is possible that the VBA code turns of the options before a delete programmatically and after the delete turns them back on. If you are not 100% the programmer, ask the other people that helped creating it.

if you really want this feature, add the precedig code to the button to make sure the featues are enabled. Of course replace false by true in the code

best regards,

Draak
0
 
AnnuAuthor Commented:
Thanks guys, but I'm stil having the same problem!

I have changed the code to:

DoCmd.SetWarnings True
Application.SetOption "Confirm Record Changes", True
Application.SetOption "Confirm Document Deletions", True
DoCmd.RunCommand acCmdDeleteRecord

but it still does not give me any confirm dialogs.

Any ideas? Thanks again.
0
 
dovholukCommented:
here's a routine to try. to use it, go to a new module and paste the following code into that NEW module. then, highlight any part of the newly pasted code and press F5 (run).  tell us the results...

Function TestWarnings()

CurrentDb.Execute "CREATE Table dovholuk (field1 text)"

DoCmd.RunSQL "insert into dovholuk (field1) values('this is a value')"
MsgBox "You should have JUST seen message box A confirming the insert, did you?"

DoCmd.SetWarnings True
DoCmd.RunSQL "insert into dovholuk (field1) values('this is a value')"
MsgBox "You should have message box B confirming the insert, did you?"

DoCmd.DeleteObject acTable, "dovholuk"

End Function

you should get two message boxes, one for the first insert and one for the second delete statement... do you get EITHER of these???

try that and get back to us...

dovholuk
0
 
TheNextStepCommented:
Try re-installing Access.
0
 
NosterdamusCommented:
Hi Annu,

Did you updrade from Access97 to Access2000 lately?

Nosterdamus
0
 
NosterdamusCommented:
Correction,

Did you updrade...

Should be:
Did you upgraded...
0
 
TheNextStepCommented:
Nosterdamus:

...upgrade.

:-D
0
 
NosterdamusCommented:
Thanx TheNextStep, it's been a looooooooooong day... ;-)
0
 
AnnuAuthor Commented:
Hi dovholuk, thanks for the help.

I ran your code and got this:

1. message to say "you are about to append 1 row(s)..."

2. your message

3. another message to say "you are about to append 1 row(s)..."

4. your message again

That's all. There was no delete message. Is something wrong?

For TheNextStep and Nosterdamus:
I have re-installed Access about 4 times! I have not upgraded from Access97 to Access2000 recently.

Thanks all for your help!
0
 
NosterdamusCommented:
Hi Annu,

In the form where your delete button is located, do:

1. Set the BeforeDelConfirm Event property to [Event Procedure].
2. Press the 3 dots to open the Form_BeforeDelConfirm sub.
3. Paste in:
Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
    ' Suppress default Delete Confirm dialog box.
    Response = acDataErrContinue     ' Display custom dialog box.
    If MsgBox("Are you sure you want to Delete?", vbOKCancel) = vbCancel Then
        Cancel = True
    End If
End Sub
4. Activate the form and try to delete records.

On deletion, you should get the following message:
"Are you sure you want to Delete?"

Do you get it?

Pls post status.

Nosterdamus
0
 
NosterdamusCommented:
Another thing that you should check...

From the main menu, select "Tools->Options" and select the "Edit/Find" tab.

Make sure that all the options in the "Confirm" section are checked (i.e. "Record Changes", Document Deletions" and "Action Queries").

Hope this helps,

Nosterdamus
0
 
dovholukCommented:
that's great! that means that the problem is NOT with the way access is set up... it must lie somewhere else though. :(

the reason you didn't get a delete confirm is because i'm stupid. i copied the insert statement instead of the delete statement! lol. oh well. the fact that access is telling you that it's going to insert records only verifies what i stated above. with that being said, i think that if you posted your code for your delete button here, i (or another expert) could find where the error is creeping into your system.  

my best guess at the moment is that you are getting some sort of runtime error that is being handled. this would prevent you from getting an error message and it could also prevent your code from setting any flags back to "normal".

post ALL the code behind your delete button (i assume there's no propriatory info in your code) and i'll take a look.

dovholuk
0
 
AnnuAuthor Commented:
Hi dovholuk, thanks for the help.

This is the code directly behind the delete button:

Private Sub cmdDelete_Click()

    fo_Form_Delete txtOrderNo

End Sub

and this is the delete procedure (in a different module):

Sub fo_Form_Delete(ChkEmpty As Control)
   
    On Error GoTo Err_fo_Form_Delete
   
    If IsNull(ChkEmpty) Then
        DoCmd.Beep
        DoCmd.CancelEvent
    Else
        DoCmd.SetWarnings True
        Application.SetOption "Confirm Record Changes", True
        Application.SetOption "Confirm Document Deletions", True
        DoCmd.RunCommand acCmdDeleteRecord
    End If
   
    Exit Sub

Err_fo_Form_Delete:

    If Err.Number = 2501 Then
        Err.Clear
    ElseIf Err.Number = 3200 Then
        DoCmd.Beep
        MsgBox Err.Description, vbInformation + vbOKOnly, "Referential Integrity Violation (Changes Allowed)"
    Else
        DoCmd.Beep
        MsgBox Err.Description
    End If
   
    Exit Sub

End Sub


ChkEmpty is used to test if the record is blank.
Hope this helps. Thanks again.

For Nosterdamus: All options in confirm section are checked. Thanks.
0
 
NosterdamusCommented:
Hi Annu,

Try the following code instead:

Sub fo_Form_Delete(ChkEmpty As Control)
   
   On Error GoTo Err_fo_Form_Delete
   
   If IsNull(ChkEmpty) Then
       DoCmd.Beep
       DoCmd.CancelEvent
   Else
       DoCmd.SetWarnings True
       Application.SetOption "Confirm Record Changes", True
       Application.SetOption "Confirm Document Deletions", True
       DoCmd.RunCommand acCmdDeleteRecord
   End If
   
Exit_fo_Form_Delete:
   Exit Sub

Err_fo_Form_Delete:

   If Err.Number = 2501 Then
       Err.Clear
   ElseIf Err.Number = 3200 Then
       DoCmd.Beep
       MsgBox Err.Description, vbInformation + vbOKOnly, "Referential Integrity Violation (Changes
Allowed)"
   Else
       DoCmd.Beep
       MsgBox "Error: " & Err.Number & vbcrlf & Err.Description
   End If
   Resume Exit_fo_Form_Delete
   
End Sub



Hope this helps,

Nosterdamus
0
 
NosterdamusCommented:
BTW, did you try compact & repair?

From the main menu, select "Tools->Database Utilities->Compact Database"
and
"Tools->Database Utilities->Repair Database"
0
 
AnnuAuthor Commented:
hi guys, I solved the problem by changing the code from

DoCmd.RunCommand acCmdDeleteRecord

to

Application.RunCommand acCmdDeleteRecord

Weird!?

I don't know who to give the points to, I think half to dovholuk and half to Nosterdamus.
0
 
LunchyCommented:
Can I suggest a three way split?

DennisBorg
dovholuk
Nosterdamus

Lunchy
Friendly Neighbourhood Community Support Moderator
0
 
AnnuAuthor Commented:
Sure Lunchy, I think that's fair since you were the first to respond :)
0
 
kodiakbearCommented:
dovholuk
Nosterdamus

Look for your points in a new question

kb
Community Support Moderator
Experts Exchange
0
 
NosterdamusCommented:
Thanx,

Nosterdamus
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 8
  • 5
  • 3
  • +6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now