asked on

Delete Button in Access 2016

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!

Jim Dettman (EE MVE)

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 Pineault

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

urjudo

ASKER

@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

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

urjudo

ASKER

@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

Gustav Brock

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

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

Open in new window

urjudo

ASKER

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

Set Records = Me.RecordsetClone what does this do?

Gustav Brock

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

urjudo

ASKER

@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

Gustav Brock

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

Open in new window

Now, delete the parent record.

urjudo

ASKER

@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

Gustav Brock

It must be like:

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

Open in new window

urjudo

ASKER

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

urjudo

ASKER

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

Gustav Brock

Great! That's the power of DAO.

urjudo

ASKER

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