Custom Sequential Numbering
in [Getting Started]
|
From: Keith Wilby on 21 Jan 2010 05:18 "John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message news:o9rfl599mnb4gkjp8br2n5euvs21ntrnmd(a)4ax.com... > On Thu, 21 Jan 2010 00:04:17 -0500, "Arvin Meyer [MVP]" > <arvinm(a)mvps.invalid> > wrote: > >>Not unless a number is deleted. That can always happen. There is never a >>100% guarantee that a record will not be deleted. Remember what I said. >>The >>largest an autonumber can be is 2,147,483,647. that 10 digits. 8,146,614 >>is >>a seed, there won't be any holes unless they are created, by adding a >>higher >>seed sometime later. > > Is that correct, Arvin? IME if you even *start* adding a new record > manually > (on a form, or directly in a table), an autonumber is generated; if you > hit > <ESC> or otherwise cancel the addition before it's saved to disk, the > autonumber gets used up and skipped... leaving a gap. > > Has this changed without my noticing? > I think that Arvin is recommending this method with the caveat that the user may create gaps by either deletion or by discarding a new but unsaved record. To me that does not satisfy the OP's requirement. Even if you disallow deletions you'll never stop users from creating new records and then changing their minds, and why should you? Keith.
From: Arvin Meyer [MVP] on 21 Jan 2010 09:35 "John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message news:o9rfl599mnb4gkjp8br2n5euvs21ntrnmd(a)4ax.com... > On Thu, 21 Jan 2010 00:04:17 -0500, "Arvin Meyer [MVP]" > <arvinm(a)mvps.invalid> > wrote: > >>Not unless a number is deleted. That can always happen. There is never a >>100% guarantee that a record will not be deleted. Remember what I said. >>The >>largest an autonumber can be is 2,147,483,647. that 10 digits. 8,146,614 >>is >>a seed, there won't be any holes unless they are created, by adding a >>higher >>seed sometime later. > > Is that correct, Arvin? IME if you even *start* adding a new record > manually > (on a form, or directly in a table), an autonumber is generated; if you > hit > <ESC> or otherwise cancel the addition before it's saved to disk, the > autonumber gets used up and skipped... leaving a gap. > > Has this changed without my noticing? Is hitting escape, not akin to deleting a record? With an Access form, or any bound form, the first character typed creates a record, does it not? <ESC> is what one would do to delete that record. I think we are saying the same thing in different ways. Autonumbers cannot be reused, whether the record is started or deleted. Once used, it's gone. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com
From: Arvin Meyer [MVP] on 21 Jan 2010 09:43 "Keith Wilby" <here(a)there.com> wrote in message news:4b581a1f$1_1(a)glkas0286.greenlnk.net... > IMHO dirtying a record but not saving it is not the same as deleting a > saved one, and users are going to wonder why the sequence is broken when > they haven't deleted anything. In fact, they're not just going to wonder > but they're going to complain quite loudly! Dirtying a record has the identical effect as deleting it. To use the paper analogy, once a restaurant check has been written on, it's dirtied, you can: 1. Throw it away 2. Leave it the way it is. 3. Change the data (to some degree) In all of those cases, the number is used. Only the first leaves a visible gap. > I also seem to remember reading somewhere, although I can't provide any > evidence, that even if you don't force Access to discard an AutoNumber, it > is still possible to have a broken sequence in an incremental AutoNumber. I don't think so except for the now fixed bug that allowed autonumbers to attempt reuse. > FWIW I don't allow deletions in systems that require sequential numbering, > rather I give the option to mark a record as "deleted" and then query > accordingly. Good move. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com
From: Gina Whipp on 21 Jan 2010 10:04 Keith, This might be what you are remembering... ***QUOTE Even if you could, autonumbers are not truely incremental. If you need something at is incremental with no possibility of a break in the numbering, do NOT use autonumbers. For example if you start a new record that has an autonumber field, then change your mind, that autonumber is 'burned' and you will have a gap. Sometimes Access will pull out an out of sequence autonumber, including even negative numbers, for no apparent reason even when set to incremental. Your best bet is to do a DMax of the primary key field in the table and add 1 to it just before saving the record. For this you need to use a form and hope that more than one person is not inputting a new record at the same time. Other DBMSs have things like Sequences that will truely produce an incremental number when needed. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. ***END QUOTE -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Keith Wilby" <here(a)there.com> wrote in message news:4b581a1f$1_1(a)glkas0286.greenlnk.net... > "Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote in message > news:%23EAZGglmKHA.1540(a)TK2MSFTNGP06.phx.gbl... >> >> "KenSheridan via AccessMonster.com" <u51882(a)uwe> wrote in message >> news:a25b114e44fa5(a)uwe... >> >>> Arvin's solution, while seeding the start number, has the disadvantage, >>> as >>> Keith has pointed out, of not guaranteeing an unbroken sequence (or a >>> sequence at all for that matter), for which an autonumber should never >>> be >>> used, an autonumber being intended to ensure arbitrary unique values and >>> nothing else (which is presumably why Microsoft changed it from >>> 'counter' >>> after version 2). >> >> If an incremental autonumber is used, unless a number is deleted, or >> someone reseeds the field again with a higher number, there will not be >> an unbroken sequence. If you mean that a number can be "lost" by starting >> and discarding a record, yes that can happen, but that's the same as >> deleting a record. >> > > IMHO dirtying a record but not saving it is not the same as deleting a > saved one, and users are going to wonder why the sequence is broken when > they haven't deleted anything. In fact, they're not just going to wonder > but they're going to complain quite loudly! > > I also seem to remember reading somewhere, although I can't provide any > evidence, that even if you don't force Access to discard an AutoNumber, it > is still possible to have a broken sequence in an incremental AutoNumber. > > FWIW I don't allow deletions in systems that require sequential numbering, > rather I give the option to mark a record as "deleted" and then query > accordingly. > > Keith.
From: Keith Wilby on 21 Jan 2010 10:53
"Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote in message news:etOFjgqmKHA.3792(a)TK2MSFTNGP02.phx.gbl... > > "Keith Wilby" <here(a)there.com> wrote in message > news:4b581a1f$1_1(a)glkas0286.greenlnk.net... > >> IMHO dirtying a record but not saving it is not the same as deleting a >> saved one, and users are going to wonder why the sequence is broken when >> they haven't deleted anything. In fact, they're not just going to wonder >> but they're going to complain quite loudly! > > Dirtying a record has the identical effect as deleting it. > I think we're talking at cross purposes; you're talking technical and I'm talking user-interface. From the user's viewpoint they are two distinct processes. You could possibly train a user to never delete a record, you could even deny them that functionality, but unless you design it out there is always the dreaded ESC key method of their changing their mind about creating the new record. I'm not challenging your logic from a technical POV but I am challenging the suitability of AutoNumbers for this purpose given the risk of user interference (for want of a better phrase) and for the reason cited by Jerry Whittle, kindly re-posted by Gina. |