Relationships?

[satty]

hey people,

im back again...i have a table called tblProduct & a table called tblProfile_PM (many to many link table) and a table called tblMembers_PM.

i have also created a form called frmProduct_Amend. on this form i have inserted a Delete Record command button. when i click this button i get a message saying "record cannot be deleted becuase of a related record in the tblProfile_PM table."

i am aware that this happens becuase the tblProfile_PM table has MemberID and ProductID within it to show the purchases of many members can by many products. the current relationship to the link table is "Inforce Refrential Integriy & Cascade Update Related Fields"

what do i need to do so that when i click the Delete button only a product is deleted and nothing from the tblProfile_PM table?

thanx again guys!


SaTTy

 

[Mudface]

I don't think you can do what you want to without violating referential integrity. If you have a certain product which has been purchased by various members, when you delete it you're going to have a link table with 'orphaned' records (ie the ProductID won't exist anymore).

One way round it is to include a Discontinued field in your Products table, just check the products that no longer exist for sale. You can then rebuild the queries etc for your forms to only show those products that do exist for sale.

 

[satty]

erm oright then.

that sounds like a good idea!

thanx!


SaTTy