Suggestions of best way to delete from subform

Question

Suggestions of best way to delete from subform

Anonymous

Hi i have a subform, i ideally want to add a command button to each of the updates within that will delete any line picked.

is there a best way to do this please?

sorry still a virtual newbioe

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

George Hepworth 22,855 Volunteer Moderator

Best is a subjective, situational judgement. Universal approval of a "best way" is not a particularly useful way to think about such tasks. Pick a method that suits your preferred way of working.

What do you mean by "each of the updates within"? I assume that the subform is displayed as a datasheet or as a continuous view because of the reference to "any line picked". Do you want the command button to appear for each record displayed?

One way you can delete records would be code like this:

    If MsgBox(Prompt:="Confirm you want to delete a useless record. ", Buttons:=vbYesNo, title:="Confirm Delete") = vbYes Then 

        CurrentDB.Execute "DELETE FROM tblYourTableNameGoesHere WHERE PrimaryKey ID = " & Me.PrimaryKeyID, DBFailOnError

    End If

You'd place that in a the click event of your command button.

0 comments

2 additional answers

Answer 1

You might like to take a look at DeleteDemo.zip in my Dropbox public databases folder at:

https://www.dropbox.com/scl/fo/0scigd3r48hx5xrev2jrf/AB0-GMdTgMAO5O1cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&dl=0

This little demo file includes an option to Delete Subform Records which illustrates how to delete the currently selected row, if any, in a subform by means of a single button in the parent form, using the following code:

Private Sub cmdDelete_Click()

    Const MESSAGE_TEXT = "Are you sure you want to delete the currently selected project assignment?"

    Dim frm As Form

    Dim varProjectID As Variant

    Dim strSQL As String

    Set frm = Me.sfcProjectAssignments.Form

    varProjectID = frm.cboProject

    If Not IsNull(varProjectID) Then

        If MsgBox(MESSAGE_TEXT, vbQuestion + vbOKCancel, "Confirm Deletion") = vbOK Then

            strSQL = "DELETE * FROM ProjectEmployees WHERE EmployeeID = " & _

                Me.EmployeeID & " AND ProjectID = " & varProjectID

            CurrentDb.Execute strSQL, dbFailOnError

            frm.Requery

        End If

    End If

End Sub

Answer 2

When you click on the Record Selector on the left side of the form, you can hit the Delete key on your keyboard and delete that row. Or click the Delete button on the ribbon. You don't need anything more.

However, if you want to delete multiple selected records then you would have to add a field (Yes/No) to the record and click the Check box. You can then build a DELETE query to delete the check records.

Share via

Suggestions of best way to delete from subform

2 additional answers