Autonumber is repeating itself 3

[jimlee]

I have 2 tables joined by the ID field (Autonumber), the autonumbering is currently up to about 4734 at the moment but for some reason when I try and create a new record it is assigning an autonumber of 3772 and therefore giving the error message
"The changes you requested where not successful because they would create duplicate values in the index, primary key......etc"

What is going on? is there any way to force the database to start again from 4735 like it's supposed to ?

Any help greatly appreciated as there are users here getting annoyed with me!!

jimlad

"There's this thing called being so open-minded your brains drop out." - Richard Dawkins

 

[jimlee]

Ok I sorted it out. My solution was to change the autonumber field (ID) to a normal numeric field (keeping it as the primary key-no dupes) and I put a bit of code in the 'after update' event of one of the required fields on the form which generates it's own unique number based the contents of the ID field like this...

me.ID = DMax("[ID]","tblTableName")

Jobs a goodun


jimlad

"There's this thing called being so open-minded your brains drop out." - Richard Dawkins

 

[MichaelRed]

see the faq's here in (Tek-Tips). There are several articles re autonumber. at least one of them may (should) be of interest.

using DMax is evem more fraught with peril in gneerating unique IDs than Autonumber.




MichaelRed

 

[Golom]

As Michael says ...

Using DMax in a multi-user environment can easily result in duplicates. If you have, or may sometime have, that situation then you should probably look at some of the FAQs on this site. Michael's faq700-184 is a good place to start.

 

[jimlee]

Thanks to both of you for your advice, I had looked at the FAQ and thought that maybe it was overkill if I could just use DMax but now I know I can't I'll go back and have a look. Mucho appreciated

jimlad

"There's this thing called being so open-minded your brains drop out." - Richard Dawkins

 

[RoyVidar]

It's a known bug

http://support.microsoft.com/default.aspx?scid=kb;en-us;884185

Check out Allen Brownes suggestion

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

Roy-Vidar

 

[malbreht]

Knowing that it's a bug helps me as well! It's exactly my case...thanks Roy for the link!

________________
Thanks,

Mare