Custom Sequential Numbering

  • Thread starter Thread starter Tara Metzger
  • Start date Start date
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
 
Arvin Meyer said:
Dirtying a record has the identical effect as deleting it.
Click to expand...

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.
 
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.
Click to expand...

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.
Click to expand...

In that we're in agreement, and my take is that this fact makes autonumbers
completely unsuitable if sequential gapless numbers are required.
 
Is hitting escape, not akin to deleting a record?
Click to expand...

No, because the record was never saved. But the Autonumber seed gets
incremented even if the record was never saved.
 
The problem issue is the abortion of a new row without abandoning
the number.
Click to expand...

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.
 
Arvin Meyer said:
I don't think so except for the now fixed bug that allowed autonumbers to
attempt reuse.
Click to expand...

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
 
In that we're in agreement, and my take is that this fact makes
autonumbers
completely unsuitable if sequential gapless numbers are required.
Click to expand...

The original request did not mention gapless as a requirement. In any case,
and system that allows deletions cannot, by definition, be guaranteed
gapless.
 
Roger's last post makes the most sense. If you are taking a number from a
paper, you do not need to generate it.

Further, any scheme for multi-users, must save the number immediately after
generation, or multiple users can conflict. The only way a custom generated
number can not have the same problem as the autonumber is if it is generated
as the last event on the form ie in the form's afterupdate event.
 
Roger Carlson said:
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.
Click to expand...

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
 
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.
Click to expand...

.... and bye, bye, performance and responsiveness from their database and their
developer/IT staff. Sigh.
 
Well, leave it to me to have the scenario that doesn't have a perfect solution. :-) You have all been extremely helpful! Looks like I'll have to come up with a solution based upon the wonderful information you have given me and possibly some of my own creativeness. The former I'm sure will be useful, the latter...we'll see :-)
Thank you again for all the dialogue! It is a tremendous help! I'll post a reply telling you what I've been able to come up with.

Tara



John W. Vinson wrote:

wrote:...
21-Jan-10

wrote

.... and bye, bye, performance and responsiveness from their database and thei
developer/IT staff. Sigh
-

John W. Vinson [MVP]

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Title Case Proper Names
http://www.eggheadcafe.com/tutorial...07f-c70a4fb08b05/title-case-proper-names.aspx
 
That still does not satisfy Tara's requirement of making the
number available to the user during the process of data entry.
Click to expand...

I fail to see the issue. Just because you collect the data to create
the record in an unbound form does not mean you don't show the user
the sequence number once you've created the full record from the
data entered in the unbound form.
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.
Click to expand...

I don't understand why it wouldn't work. The requirements in the
original post are:

1. Each time a Registration # is entered it is to increase by 1 and
it needs to show the user the Registration # they are currently
working on (in a form).

2. Complicating matters is the possibility of multiple users
entering data at the same time.

Using an Autonumber that is created only after the initial
information is entered in the unbound ADD NEW form insures that you
don't have any abandoned sequence numbers.

Once the data for the stub record is collected, you insert the new
record and load the new record in your full data editing form.
Autonumbers are quite immune to multi-user problems (they weren't
before Jet 3.x), and since you're inserting you record via a SQL
INSERT, the amount of time the back end table is locked is as tiny
as possible with Jet.

Now, if you read #1 as saying that the user needs to see the next
registration number *before* the record is created, that's
different. I don't read #1 as implying that at all, and I also don't
see why it should be a requirement.

Certainly the way I do this is that I use the unbound form to nudge
the user into avoiding entering duplicates, since after entering the
stub data, I look up all records with similar data and present them
in a subform as possible duplicates (and providing a mechanism to
abandon the add and go to the correct existing record instead). That
list of possible dupes could display the registration numbers for
the existing records.

But the details would depend on what the requirements of the
application are. I don't see any reason that a lot of cases where a
sequence is needed could not be kept as clean as possible (i.e., no
lost Autonumbers because the record was abandoned before it was ever
saved) with the unbound ADD NEW RECORD form.
 
[nothing I'm addressing]

I just wanted to say that in my newsreader, this message was
displayed with the header:

Re: I can't agree with you more about the necessity of <Arvin
Meyer [MVP]>

It made me grin and nod in agreement.
 
Re: I can't agree with you more about the necessity of <Arvin
Meyer [MVP]>

It made me grin and nod in agreement.
Click to expand...

LOL!!!

And yes, it looks the same in my Agent, and I do agree!
 
David W. Fenton said:
[nothing I'm addressing]

I just wanted to say that in my newsreader, this message was
displayed with the header:

Re: I can't agree with you more about the necessity of <Arvin
Meyer [MVP]>

It made me grin and nod in agreement.
Click to expand...

LOL. 3 newsreaders see the same. I'm using Outlook Express, John's using
Agent, and your using XNews. Apparently everyone is in agreement. My wife
doesn't always think so though.
 
I use Thunderbird and the subject gets terminated to
"I can't agree with you more about the necessity of"

So, Thunderbird would be the newsreader of choice for your wife. Since
Thunderbird does allow longer subject lines I can only guess that the presence
of the "<...>" construct is causing the truncation.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
David W. Fenton said:
[nothing I'm addressing]

I just wanted to say that in my newsreader, this message was
displayed with the header:

Re: I can't agree with you more about the necessity of <Arvin
Meyer [MVP]>

It made me grin and nod in agreement.
Click to expand...

LOL. 3 newsreaders see the same. I'm using Outlook Express, John's using
Agent, and your using XNews. Apparently everyone is in agreement. My wife
doesn't always think so though.
Click to expand...
 
Back
Top