Deleting records from a form with Primary Keys

[PGZ]

I have 2 tables a Property table and an Ownership table ,
which have distinctly different data and a common field.
My "Record Source" which populates the detail section of
the
form is an SQL Query which joins the 2 tables by the "Prop
ID"
and "Entity ID", respectively. Fields from both tables
appear on the form (via the Query).

The form is set to Allow Deletions.

The "Prop ID" field in the Property table is a Primary
Key. When
I delete a record, if the "Entity ID" field in the
Ownership
table 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 as a
Primary Key or Indexed ("No Duplicates") and delete only
the record in the
Ownership table?

I need the "Entity ID" in the Ownership table to be
a Primary Key or Indexed ("No Duplicates").

Thank you for your help,

PGZ

 

[Wayne Morgan]

If the EntityID field is a Primary Key and is linked to the PropID field
then it sounds as if you have a one-to-one relationship between these
tables. Is that what you want, only one record in the Ownership table for
each record in the Property table? If not, then EntityID can be a Primary
key, but it should be linked to a different field (not Primary key field) in
the Property table.

How are you doing the delete? If you are just clicking the red X on the
toolbar, you'll probably get just what you're getting. You may need a delete
button that will run a delete query that will delete the record in the
Ownership table with the current EntityID.