Autonumber duplicates - reseeding

[Speckly]

Hi there!

I recently converted my access database from 97 version to 2000. (I have also converted it back to 97 and forward to 2002 but I still get the same problem) I have the following problem which I can not find a resolution.

I have a function that adds a new record to a table. This normally works fine but occassionally (once a day) the .addnew record VBA code tries to create an autonumber which already exists in the table. The following message results:

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again. "

I firstly tried to compact and repair the database but this does not correct the autonumber problem.
I have been reading around this problem and the MS Article 884185 tells me that this is a bug which occurs in access 2003 (but I don't have this version). I then added a function which reseeds the autonumber field and then new records are added fine. However, this problem recurs daily and continually reseeding this table does not solve the problem for me.

I have checked that all computers that access this database (11 of them) have the Microsoft Jet 4.0 Database Engine Service Pack 8 (SP8).

Please can you advise me what I should do to fix this problem?

many thanks

 

[traingamer]

Also see thread702-1150102

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[MajP]

And maybe this one.

http://allenbrowne.com/ser-40.html

 

[Speckly]

Thanks for your help.

I have already seen the documents that these threads link to. Even in the Microsoft article 884185 the resolution is to reseed the table. I do this (and it fixes the problem) but the problem keeps recurring. I never had to reseed my tables before and now I have to do it daily. It is really annoying.

At the moment I am busy installing all MS updates / fixes /SP2 so that all the computers are up to date. Maybe I can hope that the problem will correct itself!!

Any other ideas would be welcome.

Thanks

 

[NvrSaDi]

I quit using autonumber fields because they can be difficult to manage.

You may want to consider using a DMax() function to create your own numbering system.