Custom Sequential Numbering

  • Thread starter Thread starter Tara Metzger
  • Start date Start date
T

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

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
 
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.
 
Arvin Meyer said:
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.
Click to expand...

Sequential?
 
Arvin Meyer said:
Of course, as long as you don't change the default from Incremental to
Random.
Click to expand...

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

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 :)
 
Arvin Meyer said:
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 :)
Click to expand...

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.
 
KenSheridan via AccessMonster.com said:
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
Click to expand...

Thanks for confirming that Ken, I thought I was going a bit doo-lally on
that one for a while :)

Keith.
 
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.
 
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/



KenSheridan via AccessMonster.com said:
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 said:
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,
Click to expand...
[quoted text clipped - 22 lines]
Putting Twitter Realtime Search to Work
http://www.eggheadcafe.com/tutorial...24-c9960b55b669/putting-twitter-realtime.aspx
Click to expand...
Click to expand...
Click to expand...
 
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
 
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?
Click to expand...

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

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

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?
 
Arvin Meyer said:
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.
Click to expand...

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.
 
John W. Vinson said:
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?
Click to expand...

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.
 
John W. Vinson said:
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?
Click to expand...

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

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

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

Good move.
 
Back
Top