If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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 |
#22
|
|||
|
|||
To Autonumber or not to Autonumber...
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] |
#23
|
|||
|
|||
To Autonumber or not to Autonumber...
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/ |
Thread Tools | |
Display Modes | |
|
|