To Autonumber or not to Autonumber...

[ellsanto]

I am setting up a job number database for my company. As of now the
"database" is kept in a binder. It will soon be housed on our server. Yay!

The most important field in the database is the job number. It is referenced
by our accounting software, our project management team, upper management,
etc. It is a simple four digit number that is somewhere around 9100 right
now.

What is the best way to generate that number on Access 2007? I have read
about the limitations of the AutoNumber field on multiple forums . Does
anyone have any suggestions on the best way to generate that field?

Thanks in advance for the help!

 

[Jeff Boyce]

Please do not confuse "Autonumber" with your (meaningful) [JobNumber].

Microsoft Autonumbers are intended to be used as unique row identifiers.
They are not generally fit for human consumption.

If you need a "simple four digit number ... somewhere around 9100...", add a
new field. You can name it [ProjectNumber]. You can get Access to
automatically generate the "next" number in sequence using the DMax()
function (check mvps.org/access or search on line for "custom autonumber").

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[NG]

I concur with Jeff,

I always use an autonumber as primary key field and a separate field to
store the meaningfull "human key" field. You can ensure it's unique by
creating an unique index on that field. To generate it I use a numbers
table: see my answer to the topic "Invoice number" in this discussion group.
It gives you more flexibility then using the dmax function.
--
Kind regards
Noëlla

Please do not confuse "Autonumber" with your (meaningful) [JobNumber].

Microsoft Autonumbers are intended to be used as unique row identifiers.
They are not generally fit for human consumption.

If you need a "simple four digit number ... somewhere around 9100...", add a
new field. You can name it [ProjectNumber]. You can get Access to
automatically generate the "next" number in sequence using the DMax()
function (check mvps.org/access or search on line for "custom autonumber").

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am setting up a job number database for my company. As of now the
"database" is kept in a binder. It will soon be housed on our server. Yay!

The most important field in the database is the job number. It is
referenced
by our accounting software, our project management team, upper management,
etc. It is a simple four digit number that is somewhere around 9100 right
now.

What is the best way to generate that number on Access 2007? I have read
about the limitations of the AutoNumber field on multiple forums . Does
anyone have any suggestions on the best way to generate that field?

Thanks in advance for the help!

 

[Bernard Peek]

I am setting up a job number database for my company. As of now the
"database" is kept in a binder. It will soon be housed on our server. Yay!

The most important field in the database is the job number. It is referenced
by our accounting software, our project management team, upper management,
etc. It is a simple four digit number that is somewhere around 9100 right
now.

What is the best way to generate that number on Access 2007? I have read
about the limitations of the AutoNumber field on multiple forums . Does
anyone have any suggestions on the best way to generate that field?

If there is a need to keep the run of numbers contiguous then use a
programmatic generator. If not then an ordinary autonumber field should
be sufficient.

 

[Klatuu]

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
fetch when the DMax function works so well? In addition, such a method could
cause your numbering to get out of sync if any user had a crash at the right
time.

Here is the basic concept:

=Nz(DMax("[InvoiceNumber]","tblInvoice"),0) + 1

The only issue you have to be concerned with is if you are in a multi user
environment, you need to plan for multiple users trying to create a new
invoice at the same time.
--
Dave Hargis, Microsoft Access MVP

I concur with Jeff,

I always use an autonumber as primary key field and a separate field to
store the meaningfull "human key" field. You can ensure it's unique by
creating an unique index on that field. To generate it I use a numbers
table: see my answer to the topic "Invoice number" in this discussion group.
It gives you more flexibility then using the dmax function.
--
Kind regards
Noëlla

Please do not confuse "Autonumber" with your (meaningful) [JobNumber].

Microsoft Autonumbers are intended to be used as unique row identifiers.
They are not generally fit for human consumption.

If you need a "simple four digit number ... somewhere around 9100...", add a
new field. You can name it [ProjectNumber]. You can get Access to
automatically generate the "next" number in sequence using the DMax()
function (check mvps.org/access or search on line for "custom autonumber").

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am setting up a job number database for my company. As of now the
"database" is kept in a binder. It will soon be housed on our server. Yay!

The most important field in the database is the job number. It is
referenced
by our accounting software, our project management team, upper management,
etc. It is a simple four digit number that is somewhere around 9100 right
now.

What is the best way to generate that number on Access 2007? I have read
about the limitations of the AutoNumber field on multiple forums . Does
anyone have any suggestions on the best way to generate that field?

Thanks in advance for the help!

 

[ellsanto]

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

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
fetch when the DMax function works so well? In addition, such a method could
cause your numbering to get out of sync if any user had a crash at the right
time.

Here is the basic concept:

=Nz(DMax("[InvoiceNumber]","tblInvoice"),0) + 1

The only issue you have to be concerned with is if you are in a multi user
environment, you need to plan for multiple users trying to create a new
invoice at the same time.
--
Dave Hargis, Microsoft Access MVP

I concur with Jeff,

I always use an autonumber as primary key field and a separate field to
store the meaningfull "human key" field. You can ensure it's unique by
creating an unique index on that field. To generate it I use a numbers
table: see my answer to the topic "Invoice number" in this discussion group.
It gives you more flexibility then using the dmax function.
--
Kind regards
Noëlla

Please do not confuse "Autonumber" with your (meaningful) [JobNumber].

Microsoft Autonumbers are intended to be used as unique row identifiers.
They are not generally fit for human consumption.

If you need a "simple four digit number ... somewhere around 9100...", add a
new field. You can name it [ProjectNumber]. You can get Access to
automatically generate the "next" number in sequence using the DMax()
function (check mvps.org/access or search on line for "custom autonumber").

Regards

Jeff Boyce
Microsoft Office/Access MVP


I am setting up a job number database for my company. As of now the
"database" is kept in a binder. It will soon be housed on our server. Yay!

The most important field in the database is the job number. It is
referenced
by our accounting software, our project management team, upper management,
etc. It is a simple four digit number that is somewhere around 9100 right
now.

What is the best way to generate that number on Access 2007? I have read
about the limitations of the AutoNumber field on multiple forums . Does
anyone have any suggestions on the best way to generate that field?

Thanks in advance for the help!

 

[Klatuu]

Using an autonumber field for a value of any meaning is always a bad idea.
No exceptions.

 

[Tony Toews [MVP]]

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
fetch when the DMax function works so well?

One reason is for a clients welding shop when they ran QC tests.
They wanted to ensure if that if a test was deleted, for whatever
reason, the number was reused. They never wanted to explain to the
client why there were missing numbers.

Yes, accountants would be exceedingly upset with this.

Tony

 

[Fred]

Youu next question is whether or not to use your job number as a PK. My
advice for your situation is no. Your "deleted job" is a good example of why
not. You will have had 2 different job records in existence with the same
job number,

 

[Klatuu]

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.

 

[Tony Toews [MVP]]

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

 

[Larry Daugherty]

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

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

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

 

[John W. Vinson]

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.

 

[Jeff Boyce]

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

Regards

Jeff Boyce
Microsoft Office/Access 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

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

 

[John W. Vinson]

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.

 

[Jeff Boyce]

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

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

Regards

Jeff Boyce
Microsoft Office/Access 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

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

 

[Larry Daugherty]

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

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

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/Forums.aspx/access-tablesdbdesign/20

0906/1

--
HTH

Dale Fye

Message posted via AccessMonster.com

 

[Armen Stein]

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.

I'm a little late to this party, but to me any possibility of
assigning the same key (no matter how small) is not acceptable. Our
rule for application design is "if it can happen, it will happen". So
DMax is out.

We put the Next Available number in another table. A small function
called something like GetNextNumber opens that record for Update,
notes the number for the output of the function, increments it, and
commits the update. Other users have to wait their turn until the
record is unlocked. No chance for duplicates.

I also agree with the other posts that if missing records are a
concern, then don't delete them, just mark them as inactive.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[John W. Vinson]

I'm a little late to this party, but to me any possibility of
assigning the same key (no matter how small) is not acceptable. Our
rule for application design is "if it can happen, it will happen". So
DMax is out.

We put the Next Available number in another table. A small function
called something like GetNextNumber opens that record for Update,
notes the number for the output of the function, increments it, and
commits the update. Other users have to wait their turn until the
record is unlocked. No chance for duplicates.

That's actually the method I use (for ages, from the Access 2.0 Developer's
Handbook if I recall aright). I don't post the code because it's Getz et al's
copyrighted code (though I suspect they'd forgive me)...

 

[Tony Toews [MVP]]

We put the Next Available number in another table. A small function
called something like GetNextNumber opens that record for Update,
notes the number for the output of the function, increments it, and
commits the update. Other users have to wait their turn until the
record is unlocked. No chance for duplicates.

Yup, I've done that in the past too.

Tony