Delete record from main form and related records in subform

[mseo]

hi,
I need to delete the record in form and all the related record from the subform
I don't know how to do so.
thanks

[tasawer]

Hi

Create a Delete Query based on the table used in your subform, and save it with a unique filename to suit like 'DelQry'

in my example, I will reference the tables thus: MainTable and SubTable
Primary key is referenced as RecordID.

Please substitute for your values.

DELETE MainTable.RecordID, SubTable.*
FROM MainTable INNER JOIN SubTable ON MainTable.RecordID = SubTable.RecordID
WHERE (((MainTable.RecordID)=[Forms]![MainForm]![RecordID]));

Now create a delete button on your main form.
under the ONCLICK property, hopefully you will get

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

just before these lines add

DoCmd.OpenQuery "DelQry"
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Hopefully this will do the trick.

let me know how you get on.

[mseo]

hi,
I tried to do this but when I click the button Delete the delete query is opening because of the line

DoCmd.OpenQuery "DelQry"

and this code delete one record each time and I want when I click delete in the main form the record in the first subform to be deleted and the all the related records in the second sub form
thanks really, I appreciate you help

[NeoPa]

There are two approaches you can use :

1. Design the tables to include cascaded deletes, then simply delete the record from the main table. All related subrecords will be deleted as part of the process. Before taking this course ensure this is appropriate for all your requirements as it will effect the database as a whole and not just your form.
2. Produce the Delete query in a SQL string and execute it. It is not necessary to save the query in these circumstances (not that it is necessarily a problem you understand).