Autonumber screwed up, using existing number

[MonsterMaxx]

Somehow the autonumber field has been screwed up.

When I try to add a new record it reuses a number it's already used.
It should be using 299, but it's trying to reuse 143.

If I go into the table itself and add a new record, then escape to cancel the add, then repeat I can get it to increment.
Do that enough times and I get to 299 and it'll add properly.
But when I run the form calling it, this resets back to 143.

I've tested it several times and it happens every time I use that form...which is a very simple form.

How is this even possible?
How do I fix it?

 

[Rabbie]

Just a few clarifications please. In the table have you set the index property of the field to Indexed(No Duplicates)? Is there already a current record in the table with the field set to 143?

Can you post a DB with the form and table so we can see exactly what happens and if we can recreate the problem

 

[MonsterMaxx]

Yes, it was set to no duplicates.

here's what I found.
There's a macro that's supposed to set another table's ID into this table (link to that 'other' record.) I had a typo in the macro so it was set to put that other table's ID into this table's ID.
This should produce an error and it did error when I went back and tried to reproduce it on purpose.
But what I found is that in my original macro it was setting that value in the autonumber field (which ought not be possible) and this was resetting the autonumber next number.

I was unable to reproduce it when I tried to change it so it would set the next autonumber to a valid number but I did find the cause of the problem.
Clue:In the original that was doing this both terms in the setvalue macro were set to control names on a form.

The way I got the autonumber next number up to a valid count was to create a new record, then escape. Repeat this process until the autonumber was high enough to be valid.

I'd say this is a bug in the Access code as well as my own error as no macro should have the right to reset the next autonumber, but that is what was happening.

Thanks for trying.

 

[Rabbie]

Glad to hear you have found out what was wrong.

 

[JustJoan]

I am having the same exact problem, but can't figure out the cause. It has happened to me at least once a year, and I have to start a new record, and then escape, until I catch up with the newest autonumber. This time I'm going to have to do this over 1000 times.

Looking for any assistance, and I'm a newbie on this forum so I'm sorry if I'm doing it wrong!
Joan

 

[gemma-the-husky]

this is a known access bug, on older versions of jet, caused by compact and repair not seeding the autonumber properly - see MS knowledge base - i'm told the fix is a later version of Jet

but you dont need to add hundreds of records

just do an insert query to insert one record with a high enough autonumber, then delete it manually

 

[neileg]

And then again, you shouldn't really use an autonumber for a field that has meaning to your users. The number is not guaranteed to be contiguous (as you have found), just unique.

 

[JustJoan]

Thanks gemma and neileg - I'll have IT come over and install the newer version of jet right away. The number doesn't have meaning to anyone, but is used to link records from another table - it is now linking old existing records to my new entries, since the number is no longer unique.

Thanks again!
Joan

 

[JustJoan]

Well, phooey - I already have the newest version of jet. Any other ideas?

Joan

 

[gemma-the-husky]

reseed your autonumber, and the problem should go away.

if it ever happens again, just reseed it.

have a look on the MS knowledge base, for more info