Deleting a record from a 1:Many relationship?

[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!

 

[Rick B]

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!

 

[Tom Ross]

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

 

[Linda]

-----Original Message-----
"Rick B" <[email protected]> wrote in

message
Thanks Rick - I will use the deletes code....

Where can I go to learn how to do that code?

Is there books or web sites I can go to?