How to delete all the entries in a Subform using a button on the main form??

[vid]

Hi!

Basically i have a Warranty Claim System. In the main form there is a subform called Warranty Claim Details which gives the details for each claim. Each claim corresponds to a specific Warranty ID. So basically each warranty ID(which is on the main form) has multiple entries in the subform.

Currenty I have 2 different VBA codes.
The first one allows me to delete the record in the main form. But the values are not deleted in the subform when i open it separately.

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

The second one allows me to delete the entries in the sub-form one by one but does not delete the main record ie in the main form.

Private Sub DELETE_Click()
On Error GoTo Err_DELETE_Click

Me.[Warranty Claim Detail Subform].SetFocus
With Me.[Warranty Claim Detail Subform]
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End With

What i need is that when i click the delete button on the main form, all the entries in the subform as well as the main entry gets deleted!

Please Please Please help... I've been stuck on this for days...

Thanks

 

[vbaInet]

If you set Referential Intergrity to cascade delete then when you delete records visible on your main form, it will automatically delete those linked to that record in the subform. You will find Cascade Delete by righ-clicking the join in the Relationships view

 

[vid]

Thanks a ton!!

 

[gemma-the-husky]

however, i dislike using cascade updates/deletes - you can inadvertently delete a lot of stuff you didnt mean to by accident.

if you have referential integrity set, and cascading deletes is turned off - and you try to delete the master, instead you get an error message saying "cannot delete because there are related records". Its a relatively simple matter to ask the user if he wants to delete the related records, and do this with a controlled delete query, THEN delete the master record.

 

[vbaInet]

You're welcome Vid.

however, i dislike using cascade updates/deletes - you can inadvertently delete a lot of stuff you didnt mean to by accident.

if you have referential integrity set, and cascading deletes is turned off - and you try to delete the master, instead you get an error message saying "cannot delete because there are related records". Its a relatively simple matter to ask the user if he wants to delete the related records, and do this with a controlled delete query, THEN delete the master record.

Something for you to consider Vid ^^^

 

[Thinh]

If you want more control over how the deletion occured just write your own SQL to do it.

Subform Query
dim strSql as string
strSql = "DELETE from Table Child where WarrentyID =" & me.txtWarrentyID.value
docmd.execute strSql

Main SQL Query
strsql = "DELETE from Table Parent Where WarrentyID =" & me.txtWarrentyID.value
docmd.execute strSql

me.refresh

 

[bulrush]

^^ I do what Thinh does above. The SQL is very fast.