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
  #11  
Old January 19th, 2010, 08:46 PM posted to microsoft.public.access.gettingstarted
Tara Metzger
external usenet poster
 
Posts: 19
Default Thank You

Ken, Keith & Arvin,

I can't thank you enough for all the help you provided below. I'll be working on this in the next day or two and may have additional posts to this one. For now you guys are my Hero's!

Tara



KenSheridan via AccessMonster.com wrote:

Of the approaches which have been suggested Roger Carlson's is simple
19-Jan-10

Of the approaches which have been suggested Roger Carlson's is simple and
reliable, but has the drawback that if there is a conflict the number is
incremented only when an attempt to save the record is made. This would seem
to go against your requirement that 'it needs to show the user the
Registration # they are currently working on'.

Keith's approach overcomes this by saving the new record immediately before
other data is entered, but this means that Nulls cannot be disallowed in the
non-key fields by setting their Required property to True, unless each is
also given a DefaultValue property, so this could be a problem.

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).

A solution which covers all of these points, and also allows the next number
used to be reseeded at any time can be found at:

http://community.netscape.com/n/pfx/...g=ws-msdevapps


It is a little more complex than the other solutions as it stores the latest
number in a separate database which is transparently opened and updated
exclusively in code to get the next number. Consequently only one user can
get the same number, but without the need to save the current record
immediately, so non-key fields can have their Required property as True in
the table's design.

Ken Sheridan
Stafford, England

Tara Metzger wrote:

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201001/1

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Dynamic ASP.NET Excel Workbooks In C#
http://www.eggheadcafe.com/tutorials...excel-wor.aspx
Ads
  #12  
Old January 20th, 2010, 08:53 AM posted to microsoft.public.access.gettingstarted
Keith Wilby
external usenet poster
 
Posts: 812
Default Custom Sequential Numbering

"KenSheridan via AccessMonster.com" [email protected] wrote in message
news:[email protected]

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.

  #13  
Old January 20th, 2010, 07:03 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Custom Sequential Numbering -- Why Bother?

I couldn't agree more, Roger, but "need" can in some cases be a legal
requirement. Things might have changed since I retired, though knowing how
slowly the wheels of legislation grind I doubt it, but in my own field of
work I believe it was a legal requirement that all applications made to the
authority in our quasi-judicial capacity must be numbered sequentially and
immutably in order of date of receipt, and registered as such. The law may
be behind the technology, but its still the law until changed, I'm afraid.

Ken Sheridan
Stafford, England

Roger Carlson wrote:
This is perhaps slightly off-topic, but since your question has been
thoroughly answered (with 4 separate solutions, no less) I'd like to
challenge the necessity of having an *unbroken* sequential number -- or a
sequential number at all.

Sequential numbering is really a paper-based security system. Sales Order,
Purchase Order, Checks, and the like are numbered sequentially to make sure
that someone doesn't steal one from the middle of a pile of them.
Sequential numbers highlight this immediately because it's easy to see when
a number has been skipped.

However, when your system is electronic, paper-based security is as useful
as a screen door in a submarine. It does no good, causes other design
complications, and might possibly cause harm.

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.

Even though my sample was suggested as a good solution, to my mind Arvin's
suggestion of an Autonumber field is really the best solution and forget the
sequential gaps. They aren't worth worrying over.

Hello,

[quoted text clipped - 22 lines]
Putting Twitter Realtime Search to Work
http://www.eggheadcafe.com/tutorials...-realtime.aspx


--
Message posted via http://www.accessmonster.com

  #14  
Old January 21st, 2010, 05:04 AM posted to microsoft.public.access.gettingstarted
Arvin Meyer [MVP][_2_]
external usenet poster
 
Posts: 2,310
Default Custom Sequential Numbering


"Keith Wilby" wrote in message
...

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


  #15  
Old January 21st, 2010, 05:10 AM posted to microsoft.public.access.gettingstarted
Arvin Meyer [MVP][_2_]
external usenet poster
 
Posts: 2,310
Default Custom Sequential Numbering


"KenSheridan via AccessMonster.com" [email protected] wrote in message
news:[email protected]

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


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

On Thu, 21 Jan 2010 00:04:17 -0500, "Arvin Meyer [MVP]"
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]
  #17  
Old January 21st, 2010, 09:11 AM posted to microsoft.public.access.gettingstarted
Keith Wilby
external usenet poster
 
Posts: 812
Default Custom Sequential Numbering

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

"KenSheridan via AccessMonster.com" [email protected] wrote in message
news:[email protected]

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.

  #18  
Old January 21st, 2010, 10:18 AM posted to microsoft.public.access.gettingstarted
Keith Wilby
external usenet poster
 
Posts: 812
Default Custom Sequential Numbering

"John W. Vinson" wrote in message
news
On Thu, 21 Jan 2010 00:04:17 -0500, "Arvin Meyer [MVP]"

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.

  #19  
Old January 21st, 2010, 02:35 PM 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
news
On Thu, 21 Jan 2010 00:04:17 -0500, "Arvin Meyer [MVP]"

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


  #20  
Old January 21st, 2010, 02:43 PM posted to microsoft.public.access.gettingstarted
Arvin Meyer [MVP][_2_]
external usenet poster
 
Posts: 2,310
Default Custom Sequential Numbering


"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. 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


 




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 01:12 PM.


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