Custom Sequential Numbering
in [Getting Started]
|
From: Keith Wilby on 20 Jan 2010 03:53 "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 > > Thanks for confirming that Ken, I thought I was going a bit doo-lally on that one for a while :) Keith.
From: Arvin Meyer [MVP] on 21 Jan 2010 00:04 "Keith Wilby" <here(a)there.com> wrote in message news:4b55e427$1_1(a)glkas0286.greenlnk.net... > OK I may well be being a bit thick here but the OP stated "This is a > 7-digit number (no alphas) where 8146614 is my first Registration #. Each > time a Registration # is entered it is to increase by 1 ..." Exactly. > I understand your method of creating the *first* number, 8146614, but > subsequent numbers risk being lost using AutoNumber don't they? 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. -- 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 00:10 "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. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com
From: John W. Vinson on 21 Jan 2010 01:00 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? -- John W. Vinson [MVP]
From: Keith Wilby on 21 Jan 2010 04:11
"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. |