Table is creating duplicates when it is set as the PK

[wabtrainer]

I have been using a database to add and update contact details for some time. Today I cannot add new contact details as it gives a warning "The changes you requested to the table were not successful because you would create duplicate values in the index, primary key, or relationship.
The table in question is Delegates the PK is DelegateID which is an AutoNumber data type and of course is indexed with no duplicates.
when i enter a new tuple the AutoNumber uses a number already used!!!
Any ideas??? If you want to be a bear:
Be a Grizzly!

 

[JimHorton]

Wab,
Compacting the db should take care of this. There is a thread here that discussed Autonumbers (thread named 'Are Access Reports Good Enough'). This is another of the reasons I recommend against them for pk's of tables in a relationship, I've seen this problem before and I forgot what the exact cause was, but I know that compacting will solve it.
--Jim
P.S. 'Tuples'? I haven't heard that dusty term in years!

 

[wabtrainer]

Many thanks Jim
Have already tried compact and that has not seemed to have done the trick.
I have imported data since the PK was set, and I have a feeling that this has something to do with it.
Is there a way to refresh the auto numbers and cascade the change to the n side of a 1:n relationship?
Tuples and relational algebra floating around my head are not helping to clarrify the situation!!! If you want to be a bear:
Be a Grizzly!

 

[JimHorton]

wab,
The way I would do this is to change the field to Number/Long Integer. First you need to break the relationship, then make the change to the datatype, then reset the relationship and set the 'cascade' option.

Now you can use your own numbering system and reset the data via a recordset. In the recordset, you could order the existing data ascending, or use 'inconsistent update' option, either way you'd just write new values and it should all work out, cascades and all.

I never use autonumber, and the few seconds of time spent implemening my process to replace it has saved me from this type of headache. I would recommend using your own numbering system...it's not that difficult and when done right, even in heavy-use multi-user systems, it is very stable and performs virtually as fast as Autonumber.
--Jim