Missign Autonumber records

[Eduman]

I have a table setup to list incidents and the primary key is an autonumber
field called indicent number. I have noticed going through the records,
there are missing numbers (ie. there are numbers missing from the sequential
order). The database is currently setup (I know this isn't the best
programming but I have to do it to get around our school system's computer
setup) with the whole database on the server and everyone accessing the whole
database with limited menus. Is this a record lock situation where two
people happen to try to enter a new record at the same time or is this
another issue? Some teachers have told me they entered data and it is not
there. That is how I found the missing numbers.

 

[Ken Snell [MVP]]

Autonumbers will have gaps in the sequence because, once "used", a number
cannot be reused. An autonumber is used whenever you create and save a
record, create and discard a record, delete an existing record, initiate an
append query and then cancel the query, and so on.

If you need to have a "guarantee" of sequential numbers without gaps, you
will need to use a numeric field whose value for new records is established
by programming.

 

[Nikos Yannacopoulos]

Eduman,

The gaps in the autonumber are normal; in an autonumber field, once a
number is issued it can't be used again if the record is deleted (even
if it the last one). A number is issued (and "spent") even if you just
Esc twice out of a new record in a form.
Autonumbers are not suited for sequential numbering, they are just not
meant for that, they are only meant as unique identifiers. If you
absolutely must have sequential numbering, then (assuming all data entry
is done through a form) the way to go is to use a simple number field
instead, and use an expression on the form to calculate the next
available number, like DMax("[PKField]", "MyTable") + 1. This could be
in the form control's Default value property, or you could use a macro
or some VBA code fired off a form event to set the value. In a multiuser
environment, the timing when this happens is of particular concern, in
that if you use the default value property, if a user starts to enter a
new record the next available number is calculated right as they enter
the new record; so, if another user starts a new record before the first
one has saved theirs, they will end up with the same PK value, so the
second one won't be able to save their record. To avoid this, I would
use an unbound form, and some VBA code behind a Save command button to
add the record, so the next available value can be calculated right
before the saving, rather than when entering the new record in a bound form.
Timing issues like the one I described above are quite likely the reason
why users can't find some records they (beieve they) have created; in
fact, this is much more likely a reason for that than the monolithic
multi-user structure.
That said, under such a scenario (monolithic multi-user) the real danger
is corruption; you may not experience it for a while (sometimes a months
long while!) but eventually it will strike, and when it does so once
then there is no end to it. The bottomline is, you must absolutely split
and give each user their own copy of the front end. If this impossible
(why?) then I strongly suggest you take measures to ensure there are
never more than a single user in the database at any time.

HTH,
Nikos

 

[Eduman]

Thanks for the help. The reason I have not split the database is because the
school system has so many levels of security tied in that I cannot access it
as the database keeper. They will not issue me full license over the shared
network and the updating and upkeeping need to be done by me. As for setting
up the system so that only one person can use it at a time, is there a way to
enter data and then "save" the record so that only one person at a time can
save as opposed to autosaving as a field is updated? I don't want to limit
the database to only one person opening it at a time on one machine in the
entire building. That would be counterproductive to the purpose of my
creation.

Eduman,

The gaps in the autonumber are normal; in an autonumber field, once a
number is issued it can't be used again if the record is deleted (even
if it the last one). A number is issued (and "spent") even if you just
Esc twice out of a new record in a form.
Autonumbers are not suited for sequential numbering, they are just not
meant for that, they are only meant as unique identifiers. If you
absolutely must have sequential numbering, then (assuming all data entry
is done through a form) the way to go is to use a simple number field
instead, and use an expression on the form to calculate the next
available number, like DMax("[PKField]", "MyTable") + 1. This could be
in the form control's Default value property, or you could use a macro
or some VBA code fired off a form event to set the value. In a multiuser
environment, the timing when this happens is of particular concern, in
that if you use the default value property, if a user starts to enter a
new record the next available number is calculated right as they enter
the new record; so, if another user starts a new record before the first
one has saved theirs, they will end up with the same PK value, so the
second one won't be able to save their record. To avoid this, I would
use an unbound form, and some VBA code behind a Save command button to
add the record, so the next available value can be calculated right
before the saving, rather than when entering the new record in a bound form.
Timing issues like the one I described above are quite likely the reason
why users can't find some records they (beieve they) have created; in
fact, this is much more likely a reason for that than the monolithic
multi-user structure.
That said, under such a scenario (monolithic multi-user) the real danger
is corruption; you may not experience it for a while (sometimes a months
long while!) but eventually it will strike, and when it does so once
then there is no end to it. The bottomline is, you must absolutely split
and give each user their own copy of the front end. If this impossible
(why?) then I strongly suggest you take measures to ensure there are
never more than a single user in the database at any time.

HTH,
Nikos

I have a table setup to list incidents and the primary key is an autonumber
field called indicent number. I have noticed going through the records,
there are missing numbers (ie. there are numbers missing from the sequential
order). The database is currently setup (I know this isn't the best
programming but I have to do it to get around our school system's computer
setup) with the whole database on the server and everyone accessing the whole
database with limited menus. Is this a record lock situation where two
people happen to try to enter a new record at the same time or is this
another issue? Some teachers have told me they entered data and it is not
there. That is how I found the missing numbers.

 

[Nikos Yannacopoulos]

Eduman,

One person at a time being counerproductive is the understatement of the
year! I couldn't agree more... it was a 'last resort' suggestion. If
what's preventing you from having a real mutli-user split database is
network security, then all that's required is a request to the IS
administrator for a folder with full rights to all database users; it's
not hard to do!
Even so, thought, the timing issue to avoid double issuing of a next
number is still valid; like I said in my previous post, my preferred
solution is the use of an unbound form and some simple code (which I can
help you with) to calculate the next available PK value right before the
record is saved, so the chance of double issuing is practically zero.
Your implied alternative of a locking at form level so only a user can
enter new records at a time is quite easy to implement: add a single
field (Yes/No, default No) table to record the action of a user entering
records; use the form's On Current event to check the value when going
to a new record, prevent new record enrty if the value in the table
field is Yes, set the value to Yes if previously No, reset it to No when
going to another record (which is not a new one again) or closing the
form *if* the lock was set by the same user (use a hidden control on the
form to "remember" if the lock was set by this user)... quite easy to do
with some simple code, but still only a half-measure in my humble
opinion, and still impeding productivity since you can't have several
users inserting data.

HTH,
Nikos

Thanks for the help. The reason I have not split the database is because the
school system has so many levels of security tied in that I cannot access it
as the database keeper. They will not issue me full license over the shared
network and the updating and upkeeping need to be done by me. As for setting
up the system so that only one person can use it at a time, is there a way to
enter data and then "save" the record so that only one person at a time can
save as opposed to autosaving as a field is updated? I don't want to limit
the database to only one person opening it at a time on one machine in the
entire building. That would be counterproductive to the purpose of my
creation.

:

Eduman,

The gaps in the autonumber are normal; in an autonumber field, once a
number is issued it can't be used again if the record is deleted (even
if it the last one). A number is issued (and "spent") even if you just
Esc twice out of a new record in a form.
Autonumbers are not suited for sequential numbering, they are just not
meant for that, they are only meant as unique identifiers. If you
absolutely must have sequential numbering, then (assuming all data entry
is done through a form) the way to go is to use a simple number field
instead, and use an expression on the form to calculate the next
available number, like DMax("[PKField]", "MyTable") + 1. This could be
in the form control's Default value property, or you could use a macro
or some VBA code fired off a form event to set the value. In a multiuser
environment, the timing when this happens is of particular concern, in
that if you use the default value property, if a user starts to enter a
new record the next available number is calculated right as they enter
the new record; so, if another user starts a new record before the first
one has saved theirs, they will end up with the same PK value, so the
second one won't be able to save their record. To avoid this, I would
use an unbound form, and some VBA code behind a Save command button to
add the record, so the next available value can be calculated right
before the saving, rather than when entering the new record in a bound form.
Timing issues like the one I described above are quite likely the reason
why users can't find some records they (beieve they) have created; in
fact, this is much more likely a reason for that than the monolithic
multi-user structure.
That said, under such a scenario (monolithic multi-user) the real danger
is corruption; you may not experience it for a while (sometimes a months
long while!) but eventually it will strike, and when it does so once
then there is no end to it. The bottomline is, you must absolutely split
and give each user their own copy of the front end. If this impossible
(why?) then I strongly suggest you take measures to ensure there are
never more than a single user in the database at any time.

HTH,
Nikos

I have a table setup to list incidents and the primary key is an autonumber
field called indicent number. I have noticed going through the records,
there are missing numbers (ie. there are numbers missing from the sequential
order). The database is currently setup (I know this isn't the best
programming but I have to do it to get around our school system's computer
setup) with the whole database on the server and everyone accessing the whole
database with limited menus. Is this a record lock situation where two
people happen to try to enter a new record at the same time or is this
another issue? Some teachers have told me they entered data and it is not
there. That is how I found the missing numbers.