Delete a Record from a Bound Form

[akgd99]

Hi, I have created a simple bound form to edit and add records from a table. I could not find a way to physically delete a record from the table. Looks like introducing a "Delete" Command key may a way, but I could not sort out the real way of implementing this. Any Help ? Thanks !!

 

[scriverb]

Paste the following into the AfterUpdate event procedure of a command button on your form:

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

This code will delete the current record on the form with any prompting to the user that it is going to happen. If you want to have a warning prompt to the user using ACCESS's standard prompts remove the SetWarnings commands.

This should do it for you.

Bob Scriver

 

[akgd99]

Thanks Bob,
The following Code had worked for me ,

Private Sub cmdDelete_Click()
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

End Sub

BUT NOT THE FOLLOWING

Private Sub Form_AfterUpdate()
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

End Sub

Bye

 

[scriverb]

Sorry about that akgd99. I meant the OnClick of the button.

glad it works for you.

Bob Scriver

 

[akgd99]

Hi, I still have a problem. While I could delete the records using the suggested method , when all the records are deleted and then using the Delete button is breaking the code since there are no more records. Is there any easy way to fix this ? I am looking for a solution which would not involve high profile VBA coding but something simple as bound forms. Any suggestions ?

 

[scriverb]

Paste the following code into the OnClick event procedure of your button. There are different ways to do this but I chose one that is fairly simply to implement. Others would be to make the button disabled when there are no records.
Make sure you change the name of the table in green to the name of your table.

Private Sub cmdDelete_Click()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblYourTable", dbOpenDynaset)
If rs.RecordCount > 0 Then
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
End If
rs.Close
db.Close
End Sub

Good luck

Bob Scriver

 

[Laois]

What about

If Not Me.NewRecord Then
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
End if

If there are no records and the form is open then it is on a new record.

Dermot

 

[akgd99]

Hi Bob & Laois,
Both of yoyr suggestions has worked for me. Thanks a lot.

akgd99