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.

 

[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.