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
  #1  
Old January 19th, 2010, 07:21 AM posted to microsoft.public.access.gettingstarted
Tara Metzger
external usenet poster
 
Posts: 19
Default Custom Sequential Numbering

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/tutorials...-realtime.aspx
  #2  
Old January 19th, 2010, 10:18 AM posted to microsoft.public.access.gettingstarted
Keith Wilby
external usenet poster
 
Posts: 812
Default Custom Sequential Numbering

Tara Metzger wrote in message ...
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

  #3  
Old January 19th, 2010, 01:26 PM posted to microsoft.public.access.gettingstarted
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Custom Sequential Numbering

Take a look at the multi-user example he
http://www.rogersaccesslibrary.com/f...ts.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.

Tara Metzger wrote:
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/tutorials...-realtime.aspx


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

  #4  
Old January 19th, 2010, 02:46 PM posted to microsoft.public.access.gettingstarted
Arvin Meyer [MVP][_2_]
external usenet poster
 
Posts: 2,310
Default Custom Sequential Numbering

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, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Tara Metzger wrote in message ...
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/tutorials...-realtime.aspx



  #5  
Old January 19th, 2010, 03:15 PM posted to microsoft.public.access.gettingstarted
Keith Wilby
external usenet poster
 
Posts: 812
Default Custom Sequential Numbering

"Arvin Meyer [MVP]" wrote in message
...
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?

  #6  
Old January 19th, 2010, 03:24 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
...
"Arvin Meyer [MVP]" wrote in message
...
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?


Of course, as long as you don't change the default from Incremental to
Random.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


  #7  
Old January 19th, 2010, 03:41 PM posted to microsoft.public.access.gettingstarted
Keith Wilby
external usenet poster
 
Posts: 812
Default Custom Sequential Numbering

"Arvin Meyer [MVP]" wrote in message
...
"Keith Wilby" wrote in message
...
"Arvin Meyer [MVP]" wrote in message
...
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?


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?

  #8  
Old January 19th, 2010, 05:38 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
...

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?


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?


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, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


  #9  
Old January 19th, 2010, 07:40 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Custom Sequential Numbering

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:
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/tutorials...-realtime.aspx


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

  #10  
Old January 19th, 2010, 09: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
 




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 03:36 PM.


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