Autonumber field missing numbers

[remcopeters]

I got a table with "ID" as autonumber field. However when I have ID numbers:

1
2
3
4
5
6
7

And I remove record 4, then I got

1
2
3
5
6
7

How can I make it fill up the missing "4"?

 

[John Big Booty]

This is one of the foibles of the Autonumber. Read this link for some of it's others.

Really though the Autonumber should only be used to provide a unique index for your records. If you need a sequential number you might want to look at using the DMax() function plus 1. If you search this forum you should find plenty of discussion on this topic.

 

[remcopeters]

Little bit weird that it does that, but okay, that's Microsoft.

DMax() also makes inconsistent numbering doesn't it? Because it just checks for the highest value and adds 1

 

[John Big Booty]

If you want to fill in missing numbers you will need to write some code that does the following;

1. Check if current DMax() matches the current record count.
2. If yes then simply append one to the current DMax()
3. If no, then cycle through all your records until you find the first missing number and assign that

Be aware though that this process will be come slower and slower as your records count increases.

Another option, might be to add a check field to you records. Then rather than delete the record simply check the check box to indicate that this record has become inactive and hide it rather than delete it. Then when you add a new record, simply clear the old data and append the new data.

 

[Pat Hartman]

Little bit weird that it does that, but okay, that's Microsoft.

It's not weird at all and it has nothing to do with Microsoft. You will find that every relational database on the market works the same way (Oracle, DB2, Sybase, etc.) with their version of the autonumber. The autonumber, AKA identity column, is used to provide a unique identifier - period! It is NOT a record number. Gaps are a normal occurance and nothing to worry about.

How can I make it fill up the missing "4"?

This is such a bad idea, I'm not even going to tell you how to do it. It is absolutely wrong-thinking to attempt to reassign previously assigned IDs to different records. If you are going to be developing applications with a relational database, you need to do some research and educate yourself on database design and relational theory so you understand how these things work. There's lots of threads here that point to good training resources.

 

[gemma-the-husky]

pat is right, rem

it's definitely not weird. autonumbers are your friend. if you want a "real world key" use some other data, such as account number + order number, or whatever is relevant for your table. keep the autonmuber for use in relating this table to other tables

even if you could "backfill" the blanks in your table, the new values would be out of sequence anyway - so what is the point.