Custom Sequential Numbering
in [Getting Started]
|
From: Arvin Meyer [MVP] on 19 Jan 2010 09:24 "Keith Wilby" <here(a)there.com> wrote in message news:4b55be52$1_1(a)glkas0286.greenlnk.net... > "Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote in message > news:Os2rH3QmKHA.5128(a)TK2MSFTNGP05.phx.gbl... >> 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
From: Keith Wilby on 19 Jan 2010 09:41 "Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote in message news:ejXPkMRmKHA.2132(a)TK2MSFTNGP05.phx.gbl... > "Keith Wilby" <here(a)there.com> wrote in message > news:4b55be52$1_1(a)glkas0286.greenlnk.net... >> "Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote in message >> news:Os2rH3QmKHA.5128(a)TK2MSFTNGP05.phx.gbl... >>> 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?
From: Arvin Meyer [MVP] on 19 Jan 2010 11:38 "Keith Wilby" <here(a)there.com> wrote in message news:4b55c475$1_1(a)glkas0286.greenlnk.net... >>>> 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
From: Keith Wilby on 19 Jan 2010 11:57 "Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote in message news:OqPgEXSmKHA.5728(a)TK2MSFTNGP06.phx.gbl... > "Keith Wilby" <here(a)there.com> wrote in message > news:4b55c475$1_1(a)glkas0286.greenlnk.net... > >>>>> 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.
From: KenSheridan via AccessMonster.com on 19 Jan 2010 13:40
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/forum.aspx?nav=libraryMessages&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: >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/aspnet/6100d85b-2f27-472d-af24-c9960b55b669/putting-twitter-realtime.aspx -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201001/1 |