Autonumbers are designed to guarantee unique values not necessarily
sequential ones. If a user starts to enter a record and then abandons it by
pressing the Esc key, or if a record is added and then immediately deleted
the number won't be re-used for the next record unless the database is
compacted first. If an earlier record is deleted that number won't be
reused, leaving a gap in the sequence.
In a single user environment you can generate sequential numbers when a
record is added via a form by using a uniquely indexed straightforward long
integer number field (not an autonumber) and in the form's BeforeInsert event
procedure assigning a value to the field by looking up the highest number in
the field already and adding one:
Me.[MyID] = DMax("[MyID]", "[MyTable]") + 1
In a multi-user environment on a network there is a risk of conflicts if two
users are adding records to the table simultaneously, so you have to cater
for this by handling the data error which results from the index violation,
or by other means.
However, what is the purpose of numbering the records? Does the fact that
there are breaks in the sequence matter? If you want to be able to determine
the sequence of the addition of records then time-stamping each record is a
better option and easily achieved by having a date/time field in the table
with the Now() function as its DefaultValue property in table design. If you
want to number records when they are output then you can compute the number
at runtime, regardless of the order in which the records are output, in a
query, in a computed control on a form, or even more easily in a report.
Ken Sheridan
Stafford, England
