AutoNumbers are duplicating

[ChrisSedgwick]

Hi,

I'm not sure how this is happening, however I'm hoping that someone can help.

I have a table containing a list of customers and their contact details. The table includes an AutoNumber field which is set as the Primary Key and Indexed with No Duplicates.

We have over 650 records on this table, which has been entered via a form for around 2 years.

However, this morning when we've come to add a new customer, we saw an error message saying "Can't go to the specified record" then when we try to close the form it says that it couldn't add the record due to a potential duplicate in the table.

After examining the table, I tried to enter a new customer in the table itself. However, when I create a new line, the AutoNumber generates a number that has already been used. The record this morning tried to use number 86, which as you can probably guess from the amount of customers we have in our table, 86 was used some time ago.

I don't understand how this is happening considering that its a primary key and indexed and we've been using it for around 2 years with no issues or changes made.

Could anyone shed any light on this issue?

Thanks,
Chris.

 

[moke123]

see if anything here helps.....http://allenbrowne.com/ser-40.html

 

[CJ_London]

is the data in a separate backend? You say 'we' - does that mean you have multiple users using the same front end?

If the answer is no to the first and yes to the second you have probably corrupted the database. - data should be in a separate backend and each user should have their own copy of the front end

Also do you have a backup you can test to see if the problem exists there.

From the description of the problem, the link provided by moke should resolve it

 

[ChrisSedgwick]

Hi both,

The link that moke sent did help, I did a quick Compact and Repair and it seems to have worked.

It is a split database with multiple users with access. The BE is held on our server and multiple FE copies on the users' desktops.

Not quite sure how it occurred but the Compact & Repair seems to have done the trick.

Thanks to both of you.

Chris.

 

[gemma-the-husky]

@chris

note the other thing that can happen is that the "next" autonumber can jump to a new range for no particular reason, which will be disconcerting if you are trying to use the autonumbers as a sort of sequence.