Error Message

swedish_mania

Registered User.
Local time
Today, 15:52
Joined
Feb 8, 2005
Messages
43
Ive written this error message, to prompt the user before they run a query. If they click yes, then the query runs. If they click cancel, then i want nothing to happen (query being cancelled and the other msgbox 'You have cancelled the previous operation' to not appear).

Code:
MsgBox ("You are about to delete a record. Do you wish to
 continue?"), vbExclamation + vbOKCancel, "Delete Record?"

I know the code isnt right, can someone help me correct it.

Thanks in advance.
 
In which event do you have this code?
 
Code:
Dim msgResult As VbMsgBoxResult
msgResult = MsgBox("You are about to delete a record.  Do you wish to continue?", vbExclamation + vbOKCancel, "Delete Record?")

Select Case msgResult
    Case vbOK
        'ok code goes here
    Case vbCancel
        'cancel code goes here
End Select
 
VB isnt my strong point. What is the ok/cancel code after vbok and vb cancel?

Thanks in advance.

Dont wish to complicate things, but is it possible to have this message box appear after the query is run but before the deletion takes place, to confirm whether or not the user want to perform that action. It probably is, but if its too much messing about then i'll leave it as it is..
 
Last edited:
Code in full..

Code:
Private Sub DeleteByDate_Click()
On Error GoTo Err_DeleteByDate_Click

    Dim stDocName As String
    
    Dim msgResult As VbMsgBoxResult
msgResult = MsgBox("You are about to delete a record.  Do you wish to continue?", vbExclamation + vbOKCancel, "Delete Record?")

Select Case msgResult
    Case vbOK
        'ok code goes here
    Case vbCancel
        'cancel code goes here
End Select
    
    'Runs the DeleteByDate query
    stDocName = "DeleteByDate"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_DeleteByDate_Click:
    Exit Sub

Err_DeleteByDate_Click:
    MsgBox Err.Description
    Resume Exit_DeleteByDate_Click
End Sub
 
if you simply want to delete just 1 record you can try something like this:

Code:
Private Sub DeleteItem_Click()
On Error GoTo Err_DeleteItem_Click
DoCmd.SetWarnings False
If MsgBox("Are you sure you want to delete the current item ?", vbYesNo, "Warning ...") = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
Else
DoCmd.CancelEvent
DoCmd.SetWarnings True
End If
Exit_Err_DeleteItem_Click:
Exit Sub
Err_DeleteItem_Click:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_Err_DeleteItem_Click
End Sub
 
There is two queries, delete by date and delete by dates. So the user can enter the date or between 2 dates in the dialogue box. The button on the form runs the query, but to prevent records being deleted by mistake, then i want some kind msgbox, to prompt the user, basically to confirm if this is what they want to do or not.

It works for ok, but when you click cancel the query still runs..

Thank you for your help
 
you should put your DoCmd.OpenQuery statement instead of my DoCmd.RunCommand acCmdDeleteRecord, so that if you select cancel, that statement won't run, hence your records won't be deleted.
 
It now looks like this..

Code:
Private Sub DeleteByDate_Click()
On Error GoTo Err_DeleteByDate_Click

    DoCmd.SetWarnings False
    
  If MsgBox("You are about to delete a record. Do you wish to continue?", vbYesNo, "Warning ...") = vbYes Then
    DoCmd.OpenQuery
    DoCmd.SetWarnings True
    Else
    
    DoCmd.CancelEvent
    DoCmd.SetWarnings True
  End If
    
    Dim stDocName As String
    
    'Runs the DeleteByDate query
    stDocName = "DeleteByDate"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_DeleteByDate_Click:
    Exit Sub

Err_DeleteByDate_Click:
    MsgBox Err.Description
    Resume Exit_DeleteByDate_Click
End Sub

Compile error: Argument not optional --> DoCmd.OpenQuery
 
You can not use the cancel event command for an OnClick event. Also, your code is in disarray. Try this...

Code:
Private Sub DeleteByDate_Click()
On Error GoTo Err_DeleteByDate_Click
    
    If MsgBox("Do you want to delete the record?", vbQuestion + vbYesNo, "Delete Record?") = vbYes Then
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "DeleteByDate", acViewNormal
        DoCmd.SetWarnings True
    Else
        MsgBox "Deletion aborted."
    End If

Exit_DeleteByDate_Click:
    Exit Sub

Err_DeleteByDate_Click:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_DeleteByDate_Click
    
End Sub
If that works, compare your code to mine so that you can learn from your mistake.
 
Last edited:
That code works fine. Thank you for your help, much appreciated.

I didnt know you couldnt use the cancel event command for an OnClick event. Can someone explain why? I cant see why that wouldnt work..

The code is confusing, because i kept changing things around, taking things out and adding things. I dont think 'DoCmd.OpenQuery' should be there twice. I usually run the code, if it doesnt work then i try to fix it, sometimes i do more harm than good. Thanks again..
 
Certain "form" events are able to use the cancel command, like the Before Update event because that has the "Cancel As Integer". I do not know how to explain why or why not but I know what works.

Code:
Private Sub Form_BeforeUpdate([COLOR=Blue]Cancel As Integer[/COLOR])

End Sub
 

Users who are viewing this thread

Back
Top Bottom