Primary Key Autonumbers

[BruceM]

The default value for a field applies only when the record is created. If
you have implemented Roger's system, and you "start to create" a record,
then navigate away from it, you have in fact created a record. Roger's
sample database does not behave as you describe.
But you are obviously determined to use autonumber, and to make your users
look at it. Your choice, but I wonder why you asked the question in the
first place.

 

[Guest]

Open Rogers Autonumber Single User Form and follow these steps to see what I
mean.

1) Go to the last record. The Product ID is 3.
2) Click the "New Record" navigation button.
3) Type Fred in the ProductName box. The Product ID is 4.
4) Click the "New Record" navigation button. The Product ID is 5.
5) Without typing anything, go back to Product ID 4.
6) Delete Product ID 4.
7) Product ID 5 is shown.
8) The "Left" navigation button returns you to ID 3. The right Nav button
returns you to ID 5.
9) Close the form
10) Open the form, Click the last record nav button, ID 3 is shown.
11) Click New Record. ID 4 is shown.

If I'd filled in details on ID5, before closing the form, when I created a
new record after opening the form for the second time, the ID would have been
6, with a hole at ID 4. The way I did it above, ID 4 is created as the next
record, with no holes.

The way the ID's are allocated depends on whether the form has been closed
after deleting the highest ID saved record.

I asked whether there was any reason why showing an Autonumber to the users
was a bad idea. Some reasons were given, but the only reason that really
makes sense, is if the users cannot delete records and expect sequential
numbers. If they can delete records, the whole problem of Access' autonumber
having holes seems to disappear.

My reason for asking was curiosity. Many people seemed to have something
against showing Autonumbers to users. I couldn't think of a reason why you
shouldn't.

Dave

 

[BruceM]

Why wouldn't you just edit Record 4? But then, if users are free to delete
records you have effectively given up control of the database. If deleting
a record that was just entered is a common occurence, you could add code to
requery the form and go the the last record or to a new record, but user
training may be more to the point.

If the users delete records as soon as they are entered, there will be gaps
in the autonumber. Closing and opening the form will not remove those gaps.
The problem with gaps in autonumber will not disappear. However, no matter
the system, if users can delete records at will there be gaps when they
decide to delete a few of last year's records. If data integrity is of no
concern, then I guess the numbering system isn't either. Good luck.

 

[Brendan Reynolds]

A possible work-around, if you just wanted a unique ref, and did not care
about sequential numbering, but did want to avoid negative numbers, might
look something like this ...

SELECT IIf([RandomAutoNumber]<0,"A","B") & Abs([RandomAutoNumber]) AS
UniqueRef
FROM TestTable2;