Others

Delete Linked Subform Records from an Access Main Form

John WilsonJohn Wilson Nov 20, 2026 868 views

Question details

Delete all child records linked to the current parent record from a button on an Access main form.

Delete Linked Subform Records from an Access Main Form
Product
Microsoft Access
Device & OS
not provided
Scenario
A main form contains a continuous subform displaying records related to the current parent.
Observed behavior
Using the subform Recordset.Delete method may not remove every related child record.
Before you start

In Database Tools > Relationships, verify the parent key and matching child-table field. Identify the subform control name on the main form, and confirm whether deleting all child records for a parent should include records hidden by subform filters.

Solution 1Recommended

Use a parameterized delete query from the main form

Delete child-table rows that match the current parent key, then refresh the subform.

The example assumes a Long Integer parent key, such as an AutoNumber key. Replace the table, field, and control names with the names in your database; use a matching parameter type for other key types.

This deletes every matching child-table record, including records hidden by subform filters. Test the criteria with a Select query before running the delete action.

1
Check the matching records

In Create > Query Design, add the child table and filter its parent-key field by a known parent ID. Run the Select query and verify that it returns only the intended records.

2
Add a delete button

Open the main form in Design View and add a Button. In its Property Sheet, set the On Click event to Event Procedure, then click the build button to open the VBA editor.

3
Add the delete procedure

In the button's Click procedure, use code like the following. Replace ParentID with the parent form's key control name, ChildTable with the child table name, ParentID in the SQL statement with the child table's foreign-key field, and SubformControl with the subform control name: Private Sub cmdDeleteChildren_Click() Dim qdf As DAO.QueryDef If Me.NewRecord Then Exit Sub If Me.Dirty Then Me.Dirty = False If IsNull(Me!ParentID) Then Exit Sub If MsgBox("Delete all child records for this parent?", vbYesNo + vbQuestion) <> vbYes Then Exit Sub Set qdf = CurrentDb.CreateQueryDef("", _ "PARAMETERS [pParent] Long; DELETE FROM [ChildTable] WHERE [ParentID] = [pParent];") qdf.Parameters("pParent").Value = Me!ParentID qdf.Execute dbFailOnError Me!SubformControl.Form.Requery Set qdf = Nothing End Sub

4
Test the button

Open the main form in Form View, select a parent with known child records, and click the delete button. Confirm that the subform refreshes and that child records belonging to other parents remain unchanged.

Use a parameterized delete query from the main form
Check related tables: Referential integrity may prevent deletion if the child records have dependent records, or cascade-delete settings may remove those dependents. Review the relationships before using the button.
Free Microsoft Office alternative

Work with Office files in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for working with documents, spreadsheets, and presentations. It helps you move between common Office file formats, but it does not replace Access forms or fix Access database deletion behavior.

Review exported database data in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX filesWork with Microsoft Word DOCX and PowerPoint PPTX filesUse familiar editing tools in a lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why might Recordset.Delete leave some subform records behind?

Recordset.Delete removes the current record, not automatically every record in the subform. A delete query targeting the child table and current parent key can remove all matching rows.

How do I find the subform control name?

Open the main form in Design View, select the subform control, and check its Name on the Property Sheet's Other tab. The control name may differ from the form object name.

Will the query delete records hidden by a subform filter?

Yes. The example deletes all child-table rows matching the parent key, regardless of the subform's current filter. Add and test additional criteria if only a subset should be deleted.

Why does Access refuse to delete the child records?

Related dependent records or insufficient permissions may prevent deletion. Check Database Tools > Relationships and review the table permissions and cascade-delete settings.