To Autonumber or not to Autonumber...

I would recommend against making this the default value unless you are in a
single user environment.

If in a multi-user environment, if more than one user creates a record at the
same time, they will likely both get the same number. Instead, use the forms
BeforeUpdate event to set the value of that field. This way, that number
will not be determined until just before the record is saved, virtually
eliminating the chance of duplication.

HTH
Dale


ellsanto wrote:
Perfect. Thanks, Dave. I modified the line you gave me and inserted it into
the data default value property. Works perfectly. I spent more time than I
want to admit sifting through suggestions from a bunch of sites. Thanks for
the quick easy solution.
I strongly disagree with using a table of numbers for this process. Why have
to keep up with such a table and why slow the process with an extra disc
[quoted text clipped - 47 lines]

Thanks in advance for the help!

--
HTH

Dale Fye

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200906/1

Klatuu wrote:

That is an entirely different circumstance, Tony.
Even for that, I would probably not use a table of available numbers. I
would probably use a boolean field to denote the record as deleted and when I
wanted a new record to use a previously used number, I would do a DMin
filtered for only deleted records.

In hindsight that would've been a better solution. We had occasional
problems with it.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/

IMHO the likelihood of a collision between two users (both users
finding the same value using DMAX()) is exactly the same no matter
which Form event is chosen to fire the method.

What Dale described is the best event to use to fire the method to
assure the fewest holes in the generated sequence. Abandoning a
record before saving it will not generate a new value.

HTH
--
-Larry-
--

"Dale_Fye via AccessMonster.com" u43991@uwe wrote in message
news:97a53b0387439@uwe...
I would recommend against making this the default value unless you
are in a
single user environment.

If in a multi-user environment, if more than one user creates a
record at the
same time, they will likely both get the same number. Instead, use
the forms
BeforeUpdate event to set the value of that field. This way, that
number
will not be determined until just before the record is saved,
virtually
eliminating the chance of duplication.

HTH
Dale


ellsanto wrote:
Perfect. Thanks, Dave. I modified the line you gave me and inserted
it into
the data default value property. Works perfectly. I spent more
time than I
want to admit sifting through suggestions from a bunch of sites.
Thanks for
the quick easy solution.
I strongly disagree with using a table of numbers for this
process. Why have
to keep up with such a table and why slow the process with an
extra disc
[quoted text clipped - 47 lines]

Thanks in advance for the help!

--
HTH

Dale Fye

Message posted via AccessMonster.com

http://www.accessmonster.com/Uwe/For...esign/200906/1

Larry,

I disagree. If you make this the default value for that field, it will get
computed as soon as the user gets to a new record. If they sit on that
record for 10-20 minutes, without saving the record, you have 10-20 minutes
for someone else to come in and generate the exact same number.

OTOH, if you do it in the BeforeUpdate event, you have the benefit of only
seconds (maybe mili-seconds) of time between when you generate it and save
the record (almost guaranteeing that you won't get duplicates).

Dale

Larry Daugherty wrote:
IMHO the likelihood of a collision between two users (both users
finding the same value using DMAX()) is exactly the same no matter
which Form event is chosen to fire the method.

What Dale described is the best event to use to fire the method to
assure the fewest holes in the generated sequence. Abandoning a
record before saving it will not generate a new value.

HTH
I would recommend against making this the default value unless you are in a
single user environment.
[quoted text clipped - 17 lines]

Thanks in advance for the help!

http://www.accessmonster.com/Uwe/For...esign/200906/1

--
HTH

Dale Fye

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200906/1

On Tue, 16 Jun 2009 17:57:32 GMT, "Dale_Fye via AccessMonster.com"
u43991@uwe wrote:

I disagree. If you make this the default value for that field, it will get
computed as soon as the user gets to a new record. If they sit on that
record for 10-20 minutes, without saving the record, you have 10-20 minutes
for someone else to come in and generate the exact same number.

OTOH, if you do it in the BeforeUpdate event, you have the benefit of only
seconds (maybe mili-seconds) of time between when you generate it and save
the record (almost guaranteeing that you won't get duplicates).

Alternatively, *IF* there are no required fields other than the primary key,
you can assign the value in the form's BeforeInsert event and immediately save
the record. This should leave only a millisecond or two gap, and has the
advantage that the user can actually see the new ID onscreen.
--

John W. Vinson [MVP]

Agree, am actually doing that now with one of my applications.

The down side to this is that if another user creates a record immediately
after this, and the original user decides to cancel his record (in this case
he would have to actually delete it since it has already been created), then
you have a hole in your numbering scheme (which for some users is totally
inexcusable).

Dale

John W. Vinson wrote:
I disagree. If you make this the default value for that field, it will get
computed as soon as the user gets to a new record. If they sit on that
[quoted text clipped - 4 lines]
seconds (maybe mili-seconds) of time between when you generate it and save
the record (almost guaranteeing that you won't get duplicates).

Alternatively, *IF* there are no required fields other than the primary key,
you can assign the value in the form's BeforeInsert event and immediately save
the record. This should leave only a millisecond or two gap, and has the
advantage that the user can actually see the new ID onscreen.

--
HTH

Dale Fye

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200906/1

Isn't that assessment ("inexcusable") a result of the user trying to "use a
chainsaw to drive nails?" Autonumbers are ... autonumbersg!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Dale_Fye via AccessMonster.com" u43991@uwe wrote in message
news:97bbbbd46c81b@uwe...
Agree, am actually doing that now with one of my applications.

The down side to this is that if another user creates a record immediately
after this, and the original user decides to cancel his record (in this
case
he would have to actually delete it since it has already been created),
then
you have a hole in your numbering scheme (which for some users is totally
inexcusable).

Dale

John W. Vinson wrote:
I disagree. If you make this the default value for that field, it will
get
computed as soon as the user gets to a new record. If they sit on that
[quoted text clipped - 4 lines]
seconds (maybe mili-seconds) of time between when you generate it and
save
the record (almost guaranteeing that you won't get duplicates).

Alternatively, *IF* there are no required fields other than the primary
key,
you can assign the value in the form's BeforeInsert event and immediately
save
the record. This should leave only a millisecond or two gap, and has the
advantage that the user can actually see the new ID onscreen.

--
HTH

Dale Fye

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200906/1

On Wed, 17 Jun 2009 11:48:31 GMT, "Dale_Fye via AccessMonster.com"
u43991@uwe wrote:

Agree, am actually doing that now with one of my applications.

The down side to this is that if another user creates a record immediately
after this, and the original user decides to cancel his record (in this case
he would have to actually delete it since it has already been created), then
you have a hole in your numbering scheme (which for some users is totally
inexcusable).

Yep. It has the same disadvantage as the builtin autonumber in this regard.

The only solution I've seen is simply to disallow deletion. Once a record is
created it's there for good, perhaps with a VOID flag and perhaps even a field
to explain why the record was voided.
--

John W. Vinson [MVP]

Oops, my bad! I didn't follow closely enough and thought that Autonumbers
were the topic...

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Jeff Boyce" wrote in message
...
Isn't that assessment ("inexcusable") a result of the user trying to "use
a chainsaw to drive nails?" Autonumbers are ... autonumbersg!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Dale_Fye via AccessMonster.com" u43991@uwe wrote in message
news:97bbbbd46c81b@uwe...
Agree, am actually doing that now with one of my applications.

The down side to this is that if another user creates a record
immediately
after this, and the original user decides to cancel his record (in this
case
he would have to actually delete it since it has already been created),
then
you have a hole in your numbering scheme (which for some users is totally
inexcusable).

Dale

John W. Vinson wrote:
I disagree. If you make this the default value for that field, it will
get
computed as soon as the user gets to a new record. If they sit on that
[quoted text clipped - 4 lines]
seconds (maybe mili-seconds) of time between when you generate it and
save
the record (almost guaranteeing that you won't get duplicates).

Alternatively, *IF* there are no required fields other than the primary
key,
you can assign the value in the form's BeforeInsert event and immediately
save
the record. This should leave only a millisecond or two gap, and has the
advantage that the user can actually see the new ID onscreen.

--
HTH

Dale Fye

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200906/1

Dale, you're right, of course. Thanks for catching and correcting my
blunder. I made an illogical assumption and fired from the hip. I
actually use the BeforeUupdate or BeforeInsert event. A medication I
take has a reputed side effect of impairing presence of mind. At
least the side effect is working.

--
-Larry-
--

"Dale_Fye via AccessMonster.com" u43991@uwe wrote in message
news:97b26205f8f2f@uwe...
Larry,

I disagree. If you make this the default value for that field, it
will get
computed as soon as the user gets to a new record. If they sit on
that
record for 10-20 minutes, without saving the record, you have 10-20
minutes
for someone else to come in and generate the exact same number.

OTOH, if you do it in the BeforeUpdate event, you have the benefit
of only
seconds (maybe mili-seconds) of time between when you generate it
and save
the record (almost guaranteeing that you won't get duplicates).

Dale

Larry Daugherty wrote:
IMHO the likelihood of a collision between two users (both users
finding the same value using DMAX()) is exactly the same no matter
which Form event is chosen to fire the method.

What Dale described is the best event to use to fire the method to
assure the fewest holes in the generated sequence. Abandoning a
record before saving it will not generate a new value.

HTH
I would recommend against making this the default value unless
you are in a
single user environment.
[quoted text clipped - 17 lines]

Thanks in advance for the help!


http://www.accessmonster.com/Uwe/For...lesdbdesign/20
0906/1

--
HTH

Dale Fye

Message posted via AccessMonster.com

http://www.accessmonster.com/Uwe/For...esign/200906/1