Deleting Records from a Form

[Gerald]

I have 2 tables a Property table and an Ownership table.
My Row source which populates the detail section of the
form is SQL which joins the 2 tables by the "Prop ID"
and "Entity ID", respectively. Fields from both tables
appear on the form.

The form is set to Allow Deletions.

In the Property table the "Prop ID" is a Primary Key. When
I delete a record, if the "Entity ID" in the Ownership
table in the is a Primary Key or is Indexed (no
duplicates), the respective records from both tables are
deleted.

If the "Entity ID" in the Ownership table is NOT a Primary
Key nor is Indexed only the record in the Ownership table
is deleted, which is what I want.

Why is this happening?

How can I have the "Entity ID" in the Ownership table a
Primary Key or is Index and delete only the record in the
Ownership table?

I really need the "Entity ID" in the Ownership table to be
a Primary Key or Indexed (no duplicates).

Thank you for your help,

Gerald

 

[Steve Schapel]

Gerald,

I know this isn't really what you asked... But I think it would be
easier to combine the Property table and Ownership table into one.
Why have you got this data separated into 2 tables anyway?

- Steve Schapel, Microsoft Access MVP

 

[Gerald]

The Property table holds all the static information of a
given property. The Ownership table holds the ownership
relationships and percentages of a given property along
with the same information for non-Property entities. Also
even if a Ownership record is deleted the Property must
still appear in the Property table (this is a master
table).