manual deletion of records in datasheet based on joined tables

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi,
Can someboy please explain how access decide what to delete when manual record deleting on joined tables?

the joined table is shown in a data*** where the user deletes a record using the record selector and pressing the delete key.

Main table = invoice list containing customer IDkey
linked table = customer list

the query for the data***:
select invoices.number, customer.name from invoices left join customer on invoices.ID = customer.ID

invoices is local table, customer is an external table. as consequence, relation integrity rules are disabled on the join

goal: delete invoice record, keep customer data

setup 1:
invoice ID field is primary key
customer has no primary key

what happens:
upon record deletion, the record in the customer table is deleted, the record in invoices is not.

setup 2:
invoice ID field is primary key
customer ID field is primary key

what happens:
upon record deletion, record in invoice table is deleted and record in the customer table is deleted

---
what must I change to keep the customer and drop the invoice?

the aim is also but a minimal modification of access default operations.

best regards
glenn
.

Quantcast