We help IT Professionals succeed at work.

Delete Button in Access 2016

urjudo
urjudo asked
on
Medium Priority
27 Views
Last Modified: 2020-03-21
H Experts,
I have a question about running a Delete query in VBA code in Access 2016.
below is my code:

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

   Dim Msg as String
   Dim Response as Integer

   Msg = "You are about to delete the following action from the table: " & vbCrLf & vbCrLf
   Msg = Msg & vbTab & Prod#: " & vbTab & Form_frmProduct.ProdNum & vbCrLf
   Msg = Msg & vbTab & "Entered on : " & Form_frmProduct.EnterDate & vbCrLf
   Msg = Msg & vbTab & "Entered by : " & Form_frmProduct.EnterUser & vbCrLf

  Msg = Msg & vbCrLf & "       Do you want to delete this record>"
  Response = MsgBox(Msg, vbYesNo + vbApplicationModal + vbQuestion + vbDefaultButton2, "Delete confirmation")
  If Response = vbYes then
      docmd.openQuery "qryDeleteProduct"
      Form_frmProduct.requery
      docmd.close acForm, "frmProduct"
  End if

What I want to do is once the "Delete confirmation" screen pop up, users click Yes then every will be done and close the form instead showing "You're about the run a delete query that will modify data in your table" then users have to click "Yes" then another pop up screen "You are about to delete 1 row(s) from the specified table" then users have to click "Yes" again to delete the record.  Any suggestion will be very appreciated!
Comment
Watch Question

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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

Open 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

Open in new window

Author

Commented:
@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
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
If this is the product form, and you can delete records from this, you can delete directly from the RecordsetClone:

Private Sub cmdDelete_Click()

    Dim Records  As DAO.Recordset

    Dim Msg      As String
    Dim Response As VbMsgBoxResult

    Msg = "You are about to delete the following action from the table: " & vbCrLf & vbCrLf
    Msg = Msg & vbTab & Prod#: " & vbTab & Form_frmProduct.ProdNum & vbCrLf
    Msg = Msg & vbTab & "Entered on: " & Form_frmProduct.EnterDate & vbCrLf
    Msg = Msg & vbTab & "Entered by: " & Form_frmProduct.EnterUser & vbCrLf

    Msg = Msg & vbCrLf & "       Do you want to delete this record?"
    Response = MsgBox(Msg, vbYesNo + vbQuestion + vbDefaultButton2, "Delete confirmation")

    If Response = vbYes then
        Set Records = Me.RecordsetClone
        Records.FindFirst "ProdNum = " & Form_frmProduct.ProdNum & ""
        If Not Records.NoMatch Then
            Records.Delete
        End If
        DoCmd.Close acForm, "frmProduct"
    End if

End If

Author

Commented:
@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
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

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

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

Author

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

 Set Records = Me.RecordsetClone   what does this do?
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

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

Author

Commented:
@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
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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
Now, delete the parent record.

Author

Commented:
@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
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It must be like:

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

Author

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

Author

Commented:
Thanks Experts!  Thanks for all your time and patience with my issue!
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Great! That's the power of DAO.

Author

Commented:
@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!!