To Autonumber or not to Autonumber...

[Armen Stein]

Alternatively, *IF* there are no required fields other than the primary key,
you can assign the value in the form's BeforeInsert event and immediately save
the record. This should leave only a millisecond or two gap, and has the
advantage that the user can actually see the new ID onscreen.

I'm a little late to this party, but to me any possibility of
assigning the same key (no matter how small) is not acceptable. Our
rule for application design is "if it can happen, it will happen". So
DMax is out.

We put the Next Available number in another table. A small function
called something like GetNextNumber opens that record for Update,
notes the number for the output of the function, increments it, and
commits the update. Other users have to wait their turn until the
record is unlocked. No chance for duplicates.

I also agree with the other posts that if missing records are a
concern, then don't delete them, just mark them as inactive.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[John W. Vinson]

I'm a little late to this party, but to me any possibility of
assigning the same key (no matter how small) is not acceptable. Our
rule for application design is "if it can happen, it will happen". So
DMax is out.

We put the Next Available number in another table. A small function
called something like GetNextNumber opens that record for Update,
notes the number for the output of the function, increments it, and
commits the update. Other users have to wait their turn until the
record is unlocked. No chance for duplicates.

That's actually the method I use (for ages, from the Access 2.0 Developer's
Handbook if I recall aright). I don't post the code because it's Getz et al's
copyrighted code (though I suspect they'd forgive me)...

 

[Tony Toews [MVP]]

We put the Next Available number in another table. A small function
called something like GetNextNumber opens that record for Update,
notes the number for the output of the function, increments it, and
commits the update. Other users have to wait their turn until the
record is unlocked. No chance for duplicates.

Yup, I've done that in the past too.

Tony