how delete current record

[tanha]

How can I delete the current record through VBA, after the confirmation message? mean if Yes, delete the current record, and if No, exit the sub and do nothing...

 

[boblarson]

If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
   If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
    "This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
         DoCmd.RunCommand acCmdDeleteRecord
   End If
End If

 

[tanha]

Thanks much for the code...

 

[MyTech]

Than you Bob

 

[vdanelia]

Hello Bob
I have very strange question about MsgBox
Is it possible that the text which is in MSG box ("Do you wish to delete this record?") use from table (If I create a table with warning and with alert messages)
I'm asking this question because My Language is Not Supported by VBA and I can't write warning text on my language.
Of Course I can create my own form with my text it will work too, but I was interested if it is Possible

Thank You in Advanced

 

[Dairy Farmer]

Hello Bob
I have very strange question about MsgBox
Is it possible that the text which is in MSG box ("Do you wish to delete this record?") use from table (If I create a table with warning and with alert messages)

I guess you want to reuse the code in other forms?

Make a Module like:

Function DeleteARecord
If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
   If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
    "This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
         DoCmd.RunCommand acCmdDeleteRecord
   End If
End If
End Function

Then in the On Click Event for the button in the form:

Private Sub DeleteButton_Click()
DeleteARecord
End Sub

 

[jibb]

I have tried this on my form but it always comes up with the message "The command or action 'DeleteRecord' isn't available now" - Any ideas?

 

[ddrake]

I have tried this on my form but it always comes up with the message "The command or action 'DeleteRecord' isn't available now" - Any ideas?

This will happen with a new record, i.e. a record that hasn't been saved yet. There are a few ways you could handle the UI, but I think this should work:

Private Sub Form_Current()

HandleEnabling
​

End Sub

Private Sub Form_AfterUpdate()

HandleEnabling
​

End Sub

Private Sub HandleEnabling()

Me.btnDelete.enabled = not Me.NewRecord
' enable/disable other controls here...
​

End Sub

Edit: I think you can also get this error if you try to delete a dirty record. You may need to put Me.Refresh just before the DoCmd.RunCommand acCmdDeleteRecord. Also, if it's a new record, calling Me.Refresh will save the record, so if you do that, you can ignore the part above about disabling the button...
Something like this should do the trick...

Private Sub btnDelete_Click()

If MsgBox("Are you sure you want to permanently delete this Record?", vbYesNo + vbDefaultButton2 + vbQuestion, "My App") = vbYes Then

Me.Refresh
' If the record is completely blank, refresh won't do anything, but there should be nothing to delete...
If Me.NewRecord Then Exit Sub
DoCmd.RunCommand acCmdDeleteRecord
​

End If
​

End Sub

 

[psimpson]

Does this include forms where multiple tables and possibly multiple records per table are affected?

 

[reddevil1]

Is your Command Button on a SubForm?

I dont think DoCmd.RunCommand will work if it is used with a Parent property.

 

[gemma-the-husky]

if the form is based on a complex query (eg non updateable query)- access may not be able to perform the delete

try opening the query directly, and see if you can delete a record

you can also error trap the delete function, to see exactly what error you are getting. you ought to do that anyway, to add robustness to your app.

 

[psimpson]

Thanks to all who helped. Your feedback has been very helpful.

 

[Reese]

I have a similar situation and the above has solved most of my questions. I have an additional step, however.

When I use the acCmdDeleteRecord function Access automatically prompts a warning/confirmation box, even without me including a message box. I would like to skip that step.

Before everyone jumps down my throat on how bad this is, I need this for a very specific situation that is part of a longer code. Basically the command button that will include delete is going to be invisible to the user, then called upon from a command button in a different form.

 

[MyTech]

I didn't play for a long time in Access (will be back soon though), but I think it should be something like this:

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

Good luck.

 

[Reese]

Yay! That worked. Unfortunately it led to another problem. Let's call the form with the delete button Form A and the form with the command that calls the delete button form B. Both forms will be open at the same time. The command in Form B is meant to 1) delete the record that is present in form A, 2) open up a new record in Form A and 3) close form B.

Unfortunately it the end result seems to be done in this order: 1) open up a new record in Form A, 2) delete the record that is present in form A and 3) close form B. That deletes the record I wanted to keep and open form A to. Any Ideas?

Here's Form A:

Public Sub DelClientCmd_Click()

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

End Sub

Here's Form B:

Private Sub SelectCmd_Click()

Call Forms("ZooMobile Booking Form").DelClientCmd_Click
DoCmd.OpenForm "ZooMobile Booking Form", , , "[Client_ID] = " & Me.ClientIDTxt
DoCmd.Close acForm, "Booking Forms Client Search"

End Sub

 

[MyTech]

Try changing

Call Forms("ZooMobile Booking Form").DelClientCmd_Click
DoCmd.OpenForm "ZooMobile Booking Form", , , "[Client_ID] = " & Me.ClientIDTxt
DoCmd.Close acForm, "Booking Forms Client Search"

to something like

Forms("ZooMobile Booking Form").SetFocus
Call DelClientCmd_Click
DoCmd.OpenForm "ZooMobile Booking Form", , , "[Client_ID] = " & Me.ClientIDTxt
RunCommand acCmdSaveRecord
DoCmd.Close acForm, "Booking Forms Client Search"

And, why not change

Public Sub DelClientCmd_Click()

to

Private Sub DelClientCmd_Click()

?

 

[MyTech]

Did it help?

 

[Reese]

Sorry, I didn't have a chance to try it until just now. Unfortunately it doesn't seem to work. When trying it out and/or compiling the code, it keeps getting stuck at "Call DelClientCmd_Click". The compile error is "Sub or Function not defined."

I tried playing around with the code for a bit--changing the delete command back to public, specifying which form it's from, etc. But nothing seems to be working.

 

[MyTech]

So change back
Call DelClientCmd_Click
to
Call Forms("ZooMobile Booking Form").DelClientCmd_Click

The main idea is that you SetFocus to that form beforehand.

 

[Reese]

Yes! I believe I got it. Thanks!