Better way to auto number

[GiBB]

Hello all,

Im just wondering if someone can help me with a quick question.

At the moment in a table i am using, there is an autonumber field to display
the records "reference number" however due to the nature of the data on this
table, the records quite often get deleted by the using imputting the data,
halfway though creating the record, thus big gaps in the "reference
numbers"/autonumbers.

Im just wondering if there is a better way to make a field which
automaticaly creates a unique "reference number" but without leaving a gap in
the sequence if a record is cancelled before fully completed.

A further thought, it may help that the only way a record can be
cancelled/deleted is if it is not fully completed. so for example no one can
delete a record from 6 months ago (removed all ways of the user deleting a
record) , they can only cancel one they are creating.

Thanks in advance

 

[Graham R Seach]

In Access, the Autonmber datatype is usually used as a surrogate primary
key. That being the case, it's main value is to the database - not to the
user. Therefore, it should not be displayed to the user - at all. My rule is
that if the user can't do anything with it - don't show it to them.

Since there's nothing you can do about the gaps forming in the Autonumber
datatype, use it only as a unique numbering system for the database. To show
a sequential numbering scheme to the user, create your own.

Assuming a numeric numbering scheme, to find out the next number in
sequence, you can use either of the following:

SELECT Max(Nz(myNumber, 0)) + 1 AS NextNum FROM myTable
....or...
Nz(DMax("myNumber", "myTable"), 0) + 1

But even then, you can run into concurency issues; this is when two or more
users try to create records with the same number at the same time.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[GiBB]

Thats a fair point and thanks for your advice.

due to the ammount of users trying to use this database, i think it will be
best to leave the reference numbers as a standard auto number, but i'll try
and drill it into the users not to cancel records.

thanks again

 

[scubadiver]

Virtually every table does require a unique identifier of some kind
(preferably a number) so you can't renumber rows once records are deleted.

The only real solution is to prevent the form from allowing deletions