Solved

No confirm after delete

Posted on 2001-08-06
23
836 Views
Last Modified: 2008-01-09
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
Comment
Question by:Annu
  • 8
  • 5
  • 3
  • +6
23 Comments
 
LVL 8

Accepted Solution

by:
DennisBorg earned 33 total points
ID: 6357247
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
 
LVL 8

Expert Comment

by:dovholuk
ID: 6357302
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
 
LVL 1

Expert Comment

by:Bondo
ID: 6357307
Excellent comment dovholuk
0
 
LVL 5

Expert Comment

by:Draak
ID: 6357311
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
 

Author Comment

by:Annu
ID: 6358464
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
 
LVL 8

Expert Comment

by:dovholuk
ID: 6359968
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
 
LVL 1

Expert Comment

by:TheNextStep
ID: 6360172
Try re-installing Access.
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6360378
Hi Annu,

Did you updrade from Access97 to Access2000 lately?

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6360384
Correction,

Did you updrade...

Should be:
Did you upgraded...
0
 
LVL 1

Expert Comment

by:TheNextStep
ID: 6360454
Nosterdamus:

...upgrade.

:-D
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6360485
Thanx TheNextStep, it's been a looooooooooong day... ;-)
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:Annu
ID: 6362992
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
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6363266
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
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6363278
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
 
LVL 8

Expert Comment

by:dovholuk
ID: 6363592
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
 

Author Comment

by:Annu
ID: 6371321
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
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6379487
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
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6379505
BTW, did you try compact & repair?

From the main menu, select "Tools->Database Utilities->Compact Database"
and
"Tools->Database Utilities->Repair Database"
0
 

Author Comment

by:Annu
ID: 6540501
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
 
LVL 2

Expert Comment

by:Lunchy
ID: 6541704
Can I suggest a three way split?

DennisBorg
dovholuk
Nosterdamus

Lunchy
Friendly Neighbourhood Community Support Moderator
0
 

Author Comment

by:Annu
ID: 6542252
Sure Lunchy, I think that's fair since you were the first to respond :)
0
 
LVL 1

Expert Comment

by:kodiakbear
ID: 6543626
dovholuk
Nosterdamus

Look for your points in a new question

kb
Community Support Moderator
Experts Exchange
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6543930
Thanx,

Nosterdamus
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now