Autonumber c*ck up

Hi all

I have an issue with an autonumber field in a table.

Somehow, recently, the same number has been issued twice.

OK, I've realised this is an issue so want to change the field properties to indexed with no duplicates to ensure it doesn't happen again.

However, it won't let me change this while there are duplicate numbers in the table and I am unable to modify the numbers manually.

My next thought was to delete the relevant records, however as so much is linked throughout, it won't allow me to do that either.

Any suggestions?

What database system are you using? I would think that most database systems should enforce a unique constraint on an auto-number field automatically...

I'm using Microsoft Access.

I too would have thought, that using Autonumber, it would have prevented itself from duplicating numbers.

I didn't set the properties to 'No Duplicates', but using Autonumber I wouldn't have thought it was necessary.

However, now the problem has occurred, as stated earlier, I can't delete the records due to the extensive relationships throughout, yet it won't let me manually adjust the numbers.

...and until I do one or the other, I can't implement a 'no duplicates' rule in the properties to prevent further errors.

I've tried chaning the field from autonumber to number, which allowed the editing of the numbers, yet it wouldn't then let me change it back to autonumber.

The reason this is an issue is because the field in question is a 'quote' number, therefore we don't really want duplicates.

I've decided to bite the bullet on this one and delete the field, then add it again, with 'no duplicate' properties.

Not the ideal solution, but better now than later on when there's even more to go wrong....

what happened to "My next thought was to delete the relevant records, however as so much is linked throughout, it won't allow me to do that either" ?

even if it does let you delete the column and reassign the autonumbers, won't this create an even bigger c*ck up because the linked tables will then potentially point to the wrong rows???

Thanks for your input on this - it seems ok for now - the autonumber was a 'quote' number which only relevant to a drawing number within the same table.

The autonumber field has no bearing on any of the links, so I think (hope) I've got away with that one