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

 

[BruceM via AccessMonster.com]

Take a look at the multi-user example here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395

It is similar to what Keith has described, but it uses the form's Error event
to generate a new number if the one initially assigned has been taken by
another user. There could be an accompanying message to advise the user the
number has changed.

If it is important that the number the user sees initially be unchanged,
proceed as Keith has described. You could still have code in the Error event,
but if it is ever used it would be immediately, when the record is saved
directly after being created, and before the user has done any data entry.

 

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

 

[KenSheridan via AccessMonster.com]

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...yMessages&tsn=1&tid=23839&webtag=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]

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...yMessages&tsn=1&tid=23839&webtag=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:

--



Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Dynamic ASP.NET Excel Workbooks In C#
http://www.eggheadcafe.com/tutorial...0d-94301c5edc4d/dynamic-aspnet-excel-wor.aspx

 

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

 

[KenSheridan via AccessMonster.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

 

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