Why does Access think there are still related records?

[Stapes]

Hi

I have set up a routine to merge two Customers records together when
they are found to be duplicates. The Customer may have dependent
invoices. My routine finds all the Invoices linked to the original
Customer (Account Number is the Foreign Key on the Invoices table).
This is not the Primary key on the Customer's table.
To move the Invoices, I have updated the Account Code with the chosen
Account Code. Those Invoices are now shown in as being linked to the
New Account.
However, when I go to delete the now unwanted Customers record, it
says there are related records in the Invoice table.
There are not any related records.

Why does it think there is?

I don't want to put Cascade Deletes on this - I don't want any
Invoices accidentally lost.

Do I have to delete the invoices and add them in again?

Stapes

 

[Tom van Stiphout]

On Fri, 26 Oct 2007 05:27:58 -0700, Stapes <[email protected]>
wrote:

Open the Relationships window and clear it out. Place the Customers
table on it. Click the button "Direct Relationships". Chances are
there are other relations in play.

It is VERY unusual to have the relation between Customers and Orders
while not using the Customers' PK as the FK in Orders. You better have
a really good reason for this (i.e. you are an advanced database
designer and you know what you're doing), or you should do what the
rest of the world would do.

-Tom.