A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Custom Sequential Numbering



 
 
Thread Tools Display Modes
  #21  
Old January 21st, 2010, 04:04 PM posted to microsoft.public.access.gettingstarted
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Custom Sequential Numbering

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" wrote in message
...
"Arvin Meyer [MVP]" wrote in message
...

"KenSheridan via AccessMonster.com" u51882@uwe wrote in message
news:a25b114e44fa5@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.



  #22  
Old January 21st, 2010, 04:53 PM posted to microsoft.public.access.gettingstarted
Keith Wilby
external usenet poster
 
Posts: 812
Default Custom Sequential Numbering

"Arvin Meyer [MVP]" wrote in message
...

"Keith Wilby" wrote in message
...

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.

  #23  
Old January 21st, 2010, 06:08 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Custom Sequential Numbering

On Thu, 21 Jan 2010 09:35:58 -0500, "Arvin Meyer [MVP]"
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]
  #24  
Old January 21st, 2010, 06:31 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Custom Sequential Numbering

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

  #25  
Old January 21st, 2010, 08:19 PM posted to microsoft.public.access.gettingstarted
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Custom Sequential Numbering

"Arvin Meyer [MVP]" wrote in
:

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/
  #26  
Old January 21st, 2010, 08:22 PM posted to microsoft.public.access.gettingstarted
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Custom Sequential Numbering

"KenSheridan via AccessMonster.com" u51882@uwe wrote in
news:a2739cd139c4e@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/
  #27  
Old January 21st, 2010, 10:07 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Custom Sequential Numbering

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

  #28  
Old January 22nd, 2010, 12:57 AM posted to microsoft.public.access.gettingstarted
Larry Linson
external usenet poster
 
Posts: 3,112
Default Custom Sequential Numbering

"Arvin Meyer [MVP]" wrote

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.


Arvin, in either Access 2.0 or 97, I experienced some instances of Access
Autonumbers skipping hundreds or thousands of numbers. I was the only one
working on the databases at the time, and I know that I had not done any of
the "normal causes" hundreds or thousands of times, nor had there been any
delete queries executed. Fortunately, I knew by that time not to rely on
Autonumbers being monotonically increasing, so it was not a problem to me.

And, because it was not a problem for me, I didn't bother to try to analyze
the cause other than what I said in the preceding paragraph.

Larry Linson
Microsoft Office Access MVP



  #29  
Old January 22nd, 2010, 01:03 AM posted to microsoft.public.access.gettingstarted
Arvin Meyer [MVP][_2_]
external usenet poster
 
Posts: 2,310
Default Custom Sequential Numbering


"John W. Vinson" wrote in message
...

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.


The original request did not mention gapless as a requirement. In any case,
and system that allows deletions cannot, by definition, be guaranteed
gapless.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


  #30  
Old January 22nd, 2010, 01:13 AM posted to microsoft.public.access.gettingstarted
Larry Linson
external usenet poster
 
Posts: 3,112
Default Custom Sequential Numbering -- Why Bother?

"Roger Carlson" wrote

I know there are customers who believe they "need"
sequential numbering for some reason, but I always
try to dissuade them. Sometimes it works
sometimes it doesn't, but I try.


Long ago, but not so very far away, in the days of Access 2.0, I was doing
some work for a client on the prototype of an application that presumably
was going to be developed further to cover the workflow in their entire
production process. The "little old* lady accountant" who was one of the
ones who had to approve just about had a fit of apoplexy when she saw
missing numbers and it was explained why.

* not as old then as I am now, I suspect, but a real,
old-time, green eyeshade and sleeve-garters
bookkeeper type person

That's not one of those cases where you want to have a logical discussion to
prove the client's approver wrong. It was simple to correct... slight logic
change, DMAX out of the bag of tricks, and everybody was happy. Except, in
the longer term, the contract broker for whom I was working when they found
out they weren't "in with" the IT manager as they had thought.

He just up and took another job somewhere else, didn't even bother to call
the sales person for the contract broker, and he'd been the executive
sponsor of this particular application. The new IT manager decided to move
everything from client-server to IBM AS-400.

Bye, bye, development contract.

Larry Linson
Microsoft Office Access MVP



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:43 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.