Creating a new record and autonumbering problems

[MK]

I have a database which records businesses and the activities our managers do with these businesses. I enter the data in this one form that contains subforms....and whenever a new business comes up I need to enter it into the database. The autonumbering system generates the next sequential ID number.

However, something happened recently where it skipped a number....so instead of ID#3923 it went to #3924....I deleted the record and tried it again, but then it went to #3925....it's like it won't "re-use" the number of the deleted record...even though there is no data or record because the record was deleted.....so it will say record #3923 on the bottom but it will assign ID#3926 and up. I've deleted it and tried it again a few times and now it's up to ID#3930. Does anyone know how to fix it so that ID#3923 will be assigned like it should? I can't very well report: "We have a total of 3923 businesses in the database and ID#3930 is this....doesn't make sense. Can anyone help me???? Thanks

 

[Jeff Boyce]

The Access Autonumber works fine as an arbitrary, (generally) unique row
identifier. It is mostly unfit for human consumption. Because Access
really can't tell whether related "child" table entries may have been made,
even manually, using an Autonumber like #3925, it will just "throw away" an
unused autonumber. This is by design.

Rather than rely on the Autonumber to give you a "meaningful" ID number, you
can create your own numbering scheme -- check Google.com under "custom
autonumber" for examples.

I have to point out, though, that even a "custom autonumber" routine won't
prevent what you described in your post. Here's a scenario to consider:

You add 1000 rows, using a custom numbering routine.
You find that row # 791 (let's say, ID#791) is totally wrong, and delete it.
Your "count" and your "largest ID#" are no longer in sync!
If you build a routine that goes back through and renumbers all your

IDs to "take up the slack", you've just broken any links to other tables
that used that ID#!

If you build a routine to look for "gaps" and back-fill an ID, and your

row has any date/time information, the ID#s can be sorted, but a
date/time-sorted list will have ID#s out of sequence!

Good luck

Jeff Boyce
<Access MVP>

 

[Steve Schapel]

MK,

... I can't very well report: "We have a total of 3923 businesses in the database and ID#3930 is this....doesn't make sense.

Yes, this makes perfect sense. An ID# doesn't and shouldn't have any
relationship to a count of records. Certainly this is not possible
with an AutoNumber data type, which is not guaranteed to be
sequential, and the failure to re-use a number is "by design" for data
integrity reasons. The most amazing thing about this is that you got
to 3923 records before you experienced this happening! By the way,
what happens when a business closes down... do you delete them from
the database? Even if the ID numbering went as you intended, this is
one example where you would still end up with less records than the
highest ID.

If you really care what the number is, an example of a way to handle
it is to make the ID a Number data type, and use the Default Value of
this field on your form to something like...
DMax("[ID]","YourTable")+1

- Steve Schapel, Microsoft Access MVP