Delete Button in Access 2016

Add DoCmd.SetWarning False  and DoCmd.SetWarnings True before/after where you want to stop messages from Access and then re-start them again.

Jim.

Just to show you what Jim is referring to

You have

  If Response = vbYes then
      docmd.openQuery "qryDeleteProduct"
      Form_frmProduct.requery
      docmd.close acForm, "frmProduct"
  End if

Select allOpen in new window

He is suggesting you do something like

  If Response = vbYes then
      DoCmd.SetWarning False
      docmd.openQuery "qryDeleteProduct"
      Form_frmProduct.requery
      docmd.close acForm, "frmProduct"
      DoCmd.SetWarning True
  End if

Select allOpen in new window

@Daniel:
I tried this

docmd.setwarning false
If Response = vbYes then
      docmd.openQuery "qryDeleteProduct"
      Form_frmProduct.requery
      docmd.close acForm, "frmProduct"
  End if
docmd.setwarnings true

is same as the one below (the one you just sent)

 If Response = vbYes then
      DoCmd.SetWarning False
      docmd.openQuery "qryDeleteProduct"
      Form_frmProduct.requery
      docmd.close acForm, "frmProduct"
      DoCmd.SetWarning True
  End if

or I put in the wrong spot?

thanks

@Gustav,
How do the computer know which record I want to delete?  I create the delete query is base on the ID number of the record

It is FindFirst. I guess ProdNum is the ID, if not adjust it:

Records.FindFirst "ProdNum = " & Form_frmProduct.ProdNum & "" 

Select allOpen in new window

@Gustav,
Sorry, one more question, do you mind explain to me about:

 Set Records = Me.RecordsetClone   what does this do?

It sets a reference to the records of the form. Very handy.

@Gustav,
Thanks for explained to me.  one more question.  what happen if the product form has sub form, how do I delete the record in the subfirm (the subform is in a different table ) which is attach to the record that I delete

Then, first - before you delete the parent record - clear the records of the subform:

Set Records = Me!NameOfSubformControl.Form.RecordsetClone

While Not Records.EOF
    Records.Delete
    Records.MoveNext
Wend

Select allOpen in new window

Now, delete the parent record.

@Gustav,
Sorry to bother you again.  I following your instruction, here is my code, but I got an error message "Object doesn't support this property or method".  Did I missing something?

Private Sub Command90_Click()
On Error GoTo Err_Command90_Click
 Dim Records As DAO.Recordset
 
    Dim Msg As String
    Dim Response As VbMsgBoxResult
   
    Msg = "You are about to delete the following Product record from the table:" & vbCrLf & vbCrLf
   
    Msg = Msg & vbTab & "DK:    " & vbTab & Form_frmEvent_Prod.ProdDKNO & vbCrLf
    Msg = Msg & vbTab & "Entered On:   " & Form_frmEvent_Prod.ProdDATE & vbCrLf
    Msg = Msg & vbTab & "Entered By:    " & Form_frmEvent_Prod.ProdAUSER & vbCrLf
   
    Msg = Msg & vbCrLf & "                Are your sure you want to delete this record?"
    Response = MsgBox(Msg, vbYesNo + vbApplicationModal + vbCritical + vbDefaultButton2, "Delete confirmation")
    'Response = MsgBox(Msg, vbYesNo + vbApplicationModal + vbQuestion + vbDefaultButton2, "Delete confirmation")
   
    If Response = vbYes Then
        Set Records = Me!ProdID.frmEvent_ProdActionSubfrom.RecordsetClone    
        While Not Records.EOF
            Records.Delete
            Records.MoveNext
        Wend
        Set Records = Me.RecordsetClone
        Records.FindFirst "ProdID = " & Form_frmEvent_Prod.ProdID & ""
        If Not Records.NoMatch Then
           Records.Delete
        End If
        Form_frmNew_eventDio.Requery
        DoCmd.Close acForm, "frmEvent_Prod"
    End If
   
Exit_Command90_Click:
    Exit Sub

Err_Command90_Click:
    MsgBox Err.Description
    Resume Exit_Command90_Click
End Sub

On the Subform, I only have four fields, ProdActionID (primary key), ProdID (foreignKey - same as the ProdID in ProdTable), actiontype, Date

It must be like:

Set Records = Me!<NameOfYourSubformCONTROL>.Form.RecordsetClone    

Select allOpen in new window

@Gustav,
I got it, Thanks! Working perfectly now!  Thanks again!!

Thanks Experts!  Thanks for all your time and patience with my issue!

Great! That's the power of DAO.

@Gustav,
Now I learn something new, thanks!  Your way is much better than my way and I don't have to keep creating the delete queries.  Thanks so much for showing me new things!!