To Autonumber or not to Autonumber...

On Tue, 16 Jun 2009 19:14:32 -0600, John W. Vinson
wrote:

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

On Sat, 20 Jun 2009 23:02:03 -0700, Armen Stein
wrote:

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)...
--

John W. Vinson [MVP]

Armen Stein wrote:

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
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/