Custom Sequential Numbering

[Tara Metzger]

Hello,

I'm working on a survey database in Access 07 and am in need of some of your assistance. I need to develop a Registration # starting at a certain point. 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 and it needs to show the user the Registration # they are currently working on (in a form). Complicating matters is the possibility of multiple users entering data at the same time. Can you give me some ideas as to how I can accomplish this? This Reservation # is what links most of the tables together. My Reservation table information is below.

tblReservation:
ReservationNo - PK
ContactLastName
ContactFirstName
CompanyName
ComplexName
AddressID
ContactPhone

Thank you for your help!


Submitted via EggHeadCafe - Software Developer Portal of Choice
Putting Twitter Realtime Search to Work
http://www.eggheadcafe.com/tutorial...24-c9960b55b669/putting-twitter-realtime.aspx

 

[Keith Wilby]

Hello,

I'm working on a survey database in Access 07 and am in need of some of
your assistance. I need to develop a Registration # starting at a certain
point. 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 and it needs to show the user the Registration # they are currently
working on (in a form). Complicating matters is the possibility of
multiple users entering data at the same time. Can you give me some ideas
as to how I can accomplish this? This Reservation # is what links most of
the tables together. My Reservation table information is below.

tblReservation:
ReservationNo - PK
ContactLastName
ContactFirstName
CompanyName
ComplexName
AddressID
ContactPhone

Thank you for your help!

Here's one method. Set your form's Allow Additions property to False. Put
a command button on your form and add some code to its Click event to add a
new record and then immediately save it. Assuming you have a text box
called txtReservationNo, change to suit. The code would be something like
this:

Me.txtReservationNo.DefaultValue = Nz(DMax("ReservationNo",
"tblReservation")) + 1

Me.AllowAdditions = True
DoCmd.GoToRecord , , acNewRec
Me.AllowAdditions = False

HTH - Keith.
www.keithwilby.co.uk

 

[Arvin Meyer [MVP]]

You can use an autonumber. Use an append query to seed the field in the
table with the number 8146613. The first record will start with 8146614.
After entering your first record, simply delete the seed. You're on your
way.

 

[Keith Wilby]

You can use an autonumber. Use an append query to seed the field in the
table with the number 8146613. The first record will start with 8146614.
After entering your first record, simply delete the seed. You're on your
way.

Sequential?

 

[Arvin Meyer [MVP]]

Sequential?

Of course, as long as you don't change the default from Incremental to
Random.

 

[Keith Wilby]

Of course, as long as you don't change the default from Incremental to
Random.

Am I missing a trick here Arvin? AutoNumbers are never re-used are they?
So if I dirty a record and then press ESC without saving then is that
AutoNumber value not lost for good?

 

[Arvin Meyer [MVP]]

Am I missing a trick here Arvin? AutoNumbers are never re-used are they?
So if I dirty a record and then press ESC without saving then is that
AutoNumber value not lost for good?

That's true, but not part of the requirement. The requirement was to start
an incremented record number at 8146614. If you are using an incremental
autonumber and append a dummy record with the autonumber field value being
8146613, that, and all previous numbers are remove from the 2,147,483,647
possible numbers in a long integer. The very next record will be 8146614.
The seed can then be deleted. Try it, you'll like it

 

[Keith Wilby]

That's true, but not part of the requirement. The requirement was to start
an incremented record number at 8146614. If you are using an incremental
autonumber and append a dummy record with the autonumber field value being
8146613, that, and all previous numbers are remove from the 2,147,483,647
possible numbers in a long integer. The very next record will be 8146614.
The seed can then be deleted. Try it, you'll like it

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

I understand your method of creating the *first* number, 8146614, but
subsequent numbers risk being lost using AutoNumber don't they?

Thanks for your patience

Keith.

 

[Keith Wilby]

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.

 

[Roger Carlson]

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.

 

[Roger Carlson]

I agree, and I've run into the legal requirement myself. However, more
often, it's a matter of "it's the way we've always done it".

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com
http://rogersaccessblog.blogspot.com/

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

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/tutorial...24-c9960b55b669/putting-twitter-realtime.aspx

 

[Tara Metzger]

I can't agree with you more about the necessity of sequential numbering. But, like you said this is a legal requirement. Not to mention they are assigning the Reference #'s on paper first (dept 1) then putting them into the computer, in order (dept 2) so they can then use them to track the surveys. I wish I could get away with using the autonumber, but that isn't going to work in this instance. Can't take the chance someone will eliminate a number not realizing it and everything is off from that point on.
I'm so thrilled with all your help! It's wonderful to have people like yourselves to answer questions!
Tara



Roger Carlson wrote:

I agree, and I have run into the legal requirement myself.
20-Jan-10

I agree, and I have run into the legal requirement myself. However, mor
often, it is a matter of "it is the way we have always done it"

-
--Roger Carlso
MS Access MV
www.rogersaccesslibrary.co
http://rogersaccessblog.blogspot.com/

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Ping Webservice
http://www.eggheadcafe.com/tutorial...9-4cb9-8286-aa998efc58b6/ping-webservice.aspx

 

[Arvin Meyer [MVP]]

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 [MVP]]

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.

 

[John W. Vinson]

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?

 

[Keith Wilby]

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.

 

[Keith Wilby]

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.

 

[Arvin Meyer [MVP]]

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 [MVP]]

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.

 

[Roger Carlson]

If the number is being created manually (on paper), why are you using a
generated sequence number at all? You should be using the actual number
created.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L