Autonumber Problem

[Leo_Polla_Psemata]

It is strange, all of a sudden, an autonumber field in a table, behaves wrongly
and outputs a number which is already in use.
In this case, instead of "5358" , once i entered a piece of data, it generated the number 5233 ?!

How come ? what's wrong ?

 

[burrina]

Take a look at this site, it may help. Also, I would suggest you not use subdatasheets
http://allenbrowne.com/ser-40.html

 

[ypma]

In my experience auto numbers don't duplicate , however if you imported some records with an id number ,the system then generates the next number ,this will cause a error when it reaches a number that is in use. i am only guessing as your example did not show a duplicate 5233 . If it does show two itendical records in use, then i am out of my comfort zone.
Regards Ypma.

 

[AccessBlaster]

In your example you do not show a duplicate autonumber. You show the autonumber out of sequence, which could be normal.

 

[Leo_Polla_Psemata]

No, this is a duplicate, 5233 exist , in fact all numbers exist from 1 to 5357, when I update
100 records it started from 5233. ??!!

I update this table from an append query, would this be a problem ?

 

[ypma]

I would agree that the fault could lie with your append query. Check the append query before activating and look at what id numbers are being appended ,Strictly speaking you should not need any id numbers in the append query as these will be automatically generated by the Auto numbing field .As I said I am no expert but you should have an error if you have a duplicate .
Regards Ypma

 

[smig]

if you try to force the AutoNumber to low number it might try to duplicate.
Try to force a new number Dmax() + 1 into it to get it back in order.

 

[RainLover]

AutoNumber is exactly that. It is Automatic.

If you want to over ride or otherwise manipulate it you will have problem.

Just let auto number do its own thing. You should not have a problem them.

 

[gemma-the-husky]

Autonumbers can go wrong.

Google " reseed autonumbers". MS will have a support page for it. Sorry, i am on a tablet and cant post a link.

 

[Galaxiom]

I update this table from an append query, would this be a problem ?

Yes. Appending a record with a value in the Autonumber field will usually cause an incrementing autonumber to continue up from the inserted value.

One way to fix it is to delete the record with the highest number and insert it again.

 

[RainLover]

It is strange, all of a sudden, an autonumber field in a table, behaves wrongly
and outputs a number which is already in use.
In this case, instead of "5358" , once i entered a piece of data, it generated the number 5233 ?!

How come ? what's wrong ?

There is nothing wrong. All is working as intended.

You have been doing something with the Auto number calendar. I would guess that you have added a Number via a query or perhaps simply typed a number in.

Once a column as been dedicated as Auto Number you should leave it alone. Do not add edit delete or in any way alter the contents.

Users should never see an Auto Number, ever.

EDIT 30/Dec/2014

 

[Leo_Polla_Psemata]

Hi everyone, thanks for your input
This problems is really a very strange one.
Appending records is not the problem.
I scrutinized the database and the structure, i came up to this .
I have not used the id 5421, i think i tried to tune the auto-number manually.
When i reached to 5421 I made sure that DB will not duplicate, i stopped and closed, append, everything all right, then i compacted the db. Now auto number starts from 5421.

 

[RainLover]

Leave the AutoNumber alone.

Use DMax Instead.

Is there a need for every number to be used. I would think not. Provided there are no duplicated is all you have to worry about.

Don't make things more complicates than they need be . Gaps in your numbering system is acceptable.

 

[RainLover]

Leo

Have a look at this article. It will help you understand the use of an Auto Number.


http://www.utteraccess.com/wiki/index.php/Autonumbers

 

[gemma-the-husky]

fwiw, I think RainLover is wrong. You can "poke" a specific autonumber value into a table with an append query without damaging the seed value for the further autonumbers.

The autonumber itself can go wrong though.

http://support.microsoft.com/kb/812718

This MS article explains how you can fix problems with autonumbers. I have not checked all the links in the article.

also, fwiw, In Access, you can poke an autonumber of Zero into an autonumber field, which you do not seem to be able to do with SQL tables.

 

[RainLover]

Dave

I believe you are correct.

But why do it. If you use Auto Number the way it was intended there is no need. In fact you can choose between sequential and Random.

If you do use it for a real number rather than simply a HIDDEN identifier it matters not if a number is missing.

Unless this is part of a fix that should not have happened in the first place.

Dave thanks for pointing that out.

Happy new Year's tomorrow.

 

[gemma-the-husky]

well sometimes you have to do it to fix a corruption of the sort the OP mentioned

and sometimes you need to do it for other reasons - maybe you are loading data that has some RI problems, and you need to insert a dummy record.

 

[RainLover]

This is what I call getting yourself out of the problem that you should never gotten into.

My advise is not to put your fingers where they don't belong.

I would love to see a practical example.

 

[RainLover]

I will stick to my opinion. Leave the Auto Number alone.

What possible reason would you have for changing it.

Please name one Good Reason.

 

[Galaxiom]

What possible reason would you have for changing it.

Please name one Good Reason.

Occasionally there can be a need to restore a records from a backup and designate the autonumber PK of the restored record.

Best avoided though by not deleting records that should not be deleted. MarkDeleted is a great database concept that avoids the problems, so I guess it isn't a really good reason.