Changing number of digits in Autonumber ID

[eareater]

Hi All,

My first post here in the forum...

I have "inherited" an access database from a colleague, and after a while a problem occured.
The database is frequently accessed by a large number of users, some of which creates new entries.

For the last few days, new entries have been assigned Autonumber ID's with nine digits although all old entries have only four.

I am the only person able to directly access the database (everyone else does so through Excel) and I have not changed anything.

Any ideas on how I can change the 1500 old and 50 new entries into common format with regards to Autonumber ID?

Your help is highly appreciated!

 

[Trevor G]

Welcome to the Forum,

An ID AutoNumber is incremental, you can set the properties of the ID Field to have a format of 000000

So open the table in Design View and Select the ID field, look at the properties below and make sure the New Values is set to Increment not Random, then in the format place in a number of 00000, and that should work.

 

[eareater]

Hi Trevor,

Thanks for your swift reply!

I did as you suggested, but the problem persists. New values was already set to Increment, and though I have input "0000" in Format field, new entries to the database still receive nine digits unique ID.

In the attached file "screen1" you can see the Field Properties of my ID field.

And in "screen2" it is visible how ID numbers suddenly jumped from four digits to seven!
When creating new entries, they still have seven digits.

Lastly, I suppose that once the 4/9 digits issue for new entries is solved, I should copy all the entries with 9 digit ID's in order to convert them to 4 digit ID's. Or is there an easier way to do this?

 

[Trevor G]

If some how a 9 digit number has been added then it will continue to increment from the latest number.

You have some choices but it may effect the relationships and the rest of the database.

Possible Action - If unrelated
Delete ID field, compact database then recreate the ID field

Copy all records into Temp Table not including the ID Field, Empty the original table, again compact the database and then append the records which will give you new ID numbers

Only compacting an empty table will reset the ID field to start from Zero 0.