A question on the Data type: Autonumber

[Guest]

It looks ugly to me after deleting some of the records in a form arranged by
a Autonumber primary key. These numbers are no longer serial.
I wonder if there is a way to rearrange the numbers and make my form a
little bit neat...
Any suggestions?

 

[Guest]

The Autonumber primary key should never be visible to users. It is purely for
use in database design and for setting relationships and referential
integrity etc.

If you want to have a more serial type of record identifier you should
create your own. You could have another field on your form that uses the
formula =DMax(AutonumberField) + 1 when adding a record. You could perhaps
put this formula in the Default Value property for that field. That would
reclaim the next available number if you delete records from the end of the
table. However, if you delete records at the beginning or middle of the table
you will never reclaim those ID's and probably shouldn't.

If you really wanted too, you could use code to reclaim them but I wouldn't
recommend it for many reasons...

Steve

 

[Larry Daugherty]

Steve, your heart's in the right place and your explanation is correct
and as clear as a bell. But I take exception to the use of the term
"Autonumber" to mean any thing other than an Autonumber datatype.
Part of the reason newbies get confused is that no sharp distinction
is drawn between that term and "sequence".

HTH

 

[Guest]

Try deleting the autonumber field from the table, and then replacing it with
another autonumber field with the same name.

 

[Douglas J. Steele]

Of course, that'll only work if there are no relationships between that
table and other tables (whether explicitly created or not).

If there are relationships, then any foreign keys pointing back to the table
will be suspect.

 

[Guest]

True enough. I just assumed by the wording of the question that wasn't an
issue.. but a good point.