Autonumber increment not working

[lucy_maria]

Sorry if this has been answered elsewhere, I haven't had any luck searching for a solution to this particular problem.

I have a table with an autonumber id that recently has been generating numbers which are already in the table as opposed to incrementing from the last number generated. I've managed to solve this problem by creating the record then exiting out of the table, without saving, until autonumber is back to creating an number at the end of the table, thank goodness it was a small table. However, how can I stop this from happening again? What happened to cause it and is there any solution that doesn't involve a macro or recreating the table in some form?

 

[SOS]

Use an append query to insert ONE record and make sure to include the Autonumber field in the query and set the value to the number that the next number should be. Once you do that it should work again okay.

 

[lucy_maria]

Okay, but how do I stop it from happening again?

 

[SOS]

Is the database split into a frontend/backend where each user has a copy on their desktop and the backend resides on the server?

 

[gemma-the-husky]

autonumbers shouldnt fail in this way, but this error is documented on the MS knowledge base, together with the recommended solution

I believe it has been remarked that updating Jet to the latest version fixes the error

 

[The_Doc_Man]

A bigger problem is looming here.

I understand that if you created duplicates, you have a problem. But if the number is not sequential, is THAT a problem?

Does it matter to you that the autonumber is always at the end of the table, or can it be anywhere as long as it isn't already IN the table? What you need to avoid is assigning any meaning to an autonumber key before the fact. As long as you are OK with that, carry on. If not, you need to revisit that numbering system.

 

[lucy_maria]

Is the database split into a frontend/backend where each user has a copy on their desktop and the backend resides on the server?

No, the database lives on a shared drive, but all the users work directly with said database.

autonumbers shouldnt fail in this way, but this error is documented on the MS knowledge base, together with the recommended solution

I believe it has been remarked that updating Jet to the latest version fixes the error

I'm not the system administrator, this means, for a variety of reasons, that I cannot repair the database as recommended.

A bigger problem is looming here.

I understand that if you created duplicates, you have a problem. But if the number is not sequential, is THAT a problem?

Does it matter to you that the autonumber is always at the end of the table, or can it be anywhere as long as it isn't already IN the table? What you need to avoid is assigning any meaning to an autonumber key before the fact. As long as you are OK with that, carry on. If not, you need to revisit that numbering system.

That the number is not sequential is not a problem. I did look into using autonumber's random option instead of increment but the database is used by some rather computer illiterate people and I wanted to make it as easy as possible for them to use the database. Also, if this problem is occuring with increment, would it not also occur with random?

The autonumber quite simply is a primary key which relates to reach record. The key is related to another table which itself has a autonumber primary key.

 

[Atomic Shrimp]

The autonumber quite simply is a primary key which relates to reach record. The key is related to another table which itself has a autonumber primary key.

This bit sounds like it might be a problem - are you saying you've got two tables, each with their own autonumber field, and that you're expecting the two autonumbers to progress in step with one another? They won't - or at least, there's no guarantee they will, and quite a few reasons why they might not.

One of them (in whichever table could be described as the 'parent') should be an autonumber primary key, the other (in the table that contains 'child' or subordinate records) should be a numeric field, merely storing a copy (foreign key) of the parent's autonumber PK.

On re-reading, maybe you're just saying that both tables have their own primary keys for their own purposes and that there is no expectation of relating the two primary keys to one another, in which case, just ignore everything I wrote above.

 

[gemma-the-husky]

A bigger problem is looming here.

I understand that if you created duplicates, you have a problem. But if the number is not sequential, is THAT a problem?

Does it matter to you that the autonumber is always at the end of the table, or can it be anywhere as long as it isn't already IN the table? What you need to avoid is assigning any meaning to an autonumber key before the fact. As long as you are OK with that, carry on. If not, you need to revisit that numbering system.

docman - the autonumber should initially be issued as the highest number plus 1. This seed is stored somewhere within the dbs, which is why repeatedly adding items and deleting them leads to gaps in the autonumber sequence, a source of frustration to some people who want the autonumber to be something else

however there is a circumstance in which the internal seed can be corrupted, and the seed is set to a value lower than already existing examples. This then prevents records being added because of duplicate key problems. Compact and Repair doesnt reset the autonumber correctly.

The solution is to write an update query to insert a "planned" autonumber, to reseed the counter. Then delete the inserted record.

This is documented on the MS knowledge base

As I say, I think other users have pointed out that later versions of Jet fixed this.

 

[SOS]

docman - the autonumber should initially be issued as the highest number plus 1.

Not necessarily. There are settings for it which would go against that - for example, setting it to Random.

The solution is to write an update query to insert a "planned" autonumber, to reseed the counter. Then delete the inserted record.

That is true.

As I say, I think other users have pointed out that later versions of Jet fixed this.

I don't know as I had that problem not too long ago with a 2003 database and had to reset the autonumber seed to get it to stop trying to put in an existing number.