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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
Custom Sequential Numbering
"Arvin Meyer [MVP]" wrote in message
... "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 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. |
#10
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|