Custom Sequential Numbering
in [Getting Started]
|
From: John W. Vinson on 21 Jan 2010 12:08 On Thu, 21 Jan 2010 09:35:58 -0500, "Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote: >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. Well... the record is not actually written into the table until the form is closed, you move to a different record, etc. - things that trigger the Update events. There's a record but it's not a real record in the table. But yes, you're deleting the (unsaved, incipient) 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. In that we're in agreement, and my take is that this fact makes autonumbers completely unsuitable if sequential gapless numbers are required. -- John W. Vinson [MVP]
From: KenSheridan via AccessMonster.com on 21 Jan 2010 12:31 Keith: While I'm essentially in your camp on this one, in that I don't think one can really say that the abortion of the insertion of a new row into a table and the deletion of an existing row are the same, either technically or conceptually, its worth pointing out that on a pragmatic level it is actually very difficult to cater for Tara's requirements fully whichever approach is adopted. The problem issue is the abortion of a new row without abandoning the number. An autonumber fails on this count of course. Roger's doesn't but fails to meet Tara's need for the number to be known to the user while entering the other data for the row. Your solution covers this, but means allowing Nulls in all non-key columns, or giving each a default value, both of which I'd be unhappy about. Mine acts like an autonumber in that an aborted row means the number is lost, though can be 'reseeded' if no other user has begun to insert a row. While it wouldn't be difficult to handle this if only one user is inserting a new row, the whole raison d'être of the more complex approach rather than a simple DMax call is to cater for simultaneous multiple inserts of course. I could cater for this in mine by excluding other users from the external database, in which the number is stored, from the time one user begins to insert a row until they either save or abandon it, but that would be unduly restrictive to my mind. The other option of restoring the externally stored number to the value it had before a user began to insert a row is not feasible as one or more users might well have inserted rows in the meantime and thus used the subsequent numbers. The only other option which occurs to me would be for the number generation routine to re-use any gaps which had been created in the sequence, which would be simple to do by storing all used numbers in the external database rather than just the latest one, but that loses the sequentiality of the number generation process, so can't be considered a serious contender. I don't think there is a satisfactory solution which totally covers all of Tara's requirements in fact; the inherent paradox is just too fundamental as far as I can see. I'd be delighted to be corrected, though! Ken Sheridan Stafford, England Keith Wilby wrote: >>> 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. -- Message posted via http://www.accessmonster.com
From: David W. Fenton on 21 Jan 2010 14:19 "Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote in news:eiwCIcqmKHA.760(a)TK2MSFTNGP02.phx.gbl: > Is hitting escape, not akin to deleting a record? No, because the record was never saved. But the Autonumber seed gets incremented even if the record was never saved. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: David W. Fenton on 21 Jan 2010 14:22 "KenSheridan via AccessMonster.com" <u51882(a)uwe> wrote in news:a2739cd139c4e(a)uwe: > The problem issue is the abortion of a new row without abandoning > the number. If you use an unbound field to collect the data to create the new record, it will be "abandonable" without touching the Autonumber value. Indeed, this is how most of my apps do their NEW RECORD creation for entities of any level of complexity. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: KenSheridan via AccessMonster.com on 21 Jan 2010 16:07
That still does not satisfy Tara's requirement of making the number available to the user during the process of data entry. That this precludes aborting the insertion of a new row while maintaining sequentiality is the fundamental paradox here which is impossible to obviate satisfactorily as far as I can see. If you take that requirement out of the equation then Roger's method handles things very simply and reliably. Ken Sheridan Stafford, England David W. Fenton wrote: >> The problem issue is the abortion of a new row without abandoning >> the number. > >If you use an unbound field to collect the data to create the new >record, it will be "abandonable" without touching the Autonumber >value. > >Indeed, this is how most of my apps do their NEW RECORD creation for >entities of any level of complexity. > -- Message posted via http://www.accessmonster.com |