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

<Tara Metzger> wrote in message news:(E-Mail Removed)...
> 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

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 news:(E-Mail Removed)...
> 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

"Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?

"Keith Wilby" <(E-Mail Removed)> wrote in message
news:4b55be52$(E-Mail Removed)...
> "Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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

"Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "Keith Wilby" <(E-Mail Removed)> wrote in message
> news:4b55be52$(E-Mail Removed)...
>> "Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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?

"Keith Wilby" <(E-Mail Removed)> wrote in message
news:4b55c475$(E-Mail Removed)...

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

"Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "Keith Wilby" <(E-Mail Removed)> wrote in message
> news:4b55c475$(E-Mail Removed)...
>
>>>>> 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.

"KenSheridan via AccessMonster.com" <u51882@uwe> wrote in message
news:a25b114e44fa5@uwe...
>
> 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.

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.

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com
http://rogersaccessblog.blogspot.com/




<Tara Metzger> wrote in message news:(E-Mail Removed)...
> 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