Deleting a record from a 1:Many relationship?

  • Thread starter Thread starter Linda
  • Start date Start date
L

Linda

I need to setup a button to delete the current record in
a form and all the sub-form and related records to that
record. It is the ContractNum which is related to a
VendorID and VenderFee details (the Many side)....What
would the code be to make sure all details of the
ContractNum are removed? Where can I get more info on
coding these types of problems?

You guys are great, but I need to learn how to think out
the solutions too!

Thanks in advance for all your help!
 
If your relationship is setup with cascade deletes, deleting the primary
record will automatically delete all the associated records. Be very
careful using this. Make sure it is what you want to do.

Rick B


I need to setup a button to delete the current record in
a form and all the sub-form and related records to that
record. It is the ContractNum which is related to a
VendorID and VenderFee details (the Many side)....What
would the code be to make sure all details of the
ContractNum are removed? Where can I get more info on
coding these types of problems?

You guys are great, but I need to learn how to think out
the solutions too!

Thanks in advance for all your help!
 
If you don't just have two related tables but several places where the
contract num might appear you might need a set of delete queries. Also the
cascading deletes can be scary. This gives you a little more control. You
could add message boxes or whatever to advise the user what is happening.


first collect the ContractNum from your main form (assuming it is a text
string--different quoting if it is numeric)

stContractNum = Me.txtContractNum (or whatever your control is called)

Then run several delete queries (one for each table to be cleared of theis
contractnum)

DoCmd.RunSQL "DELETE * FROM [tablename1] WHERE ContractNum = '" &
stContractNum & "' ", no
DoCmd.RunSQL "DELETE * FROM [tablename2] WHERE ContractNum = '" &
stContractNum & "' ", no
DoCmd.RunSQL "DELETE * FROM [tablename3] WHERE ContractNum = '" &
stContractNum & "' ", no
 
Back
Top