Autonumber repeats exisitng values

[Vladislav]

My customers have reported strange behaviour of the locally used modules
for running the register on stroke patients, specifically, adding new
patient to the register. When analising a sample, I have found that an
autonumber variable (id) doesn't behave as expected. At some instant
after the record with id=180, the next record is coming with id=158.
I can't find any reasonable explanation. Could anybody?
V.M.

[Tim Marshall]

Vladislav wrote:

My customers have reported strange behaviour of the locally used modules
for running the register on stroke patients, specifically, adding new
patient to the register. When analising a sample, I have found that an
autonumber variable (id) doesn't behave as expected. At some instant
after the record with id=180, the next record is coming with id=158.
I can't find any reasonable explanation. Could anybody?
V.M.

I did a recent search of the newsgroup on google for some information on
alternatives to sequences other than using autonumber. There are some
circumstances when autonumber will start generating numbers randomly.
Replication, is one instance, I seem to recall.

As long as your application does not depend upon the autonumber for
ordering records, you're fine. If you need an ascending sequence
number, I'd be curious as to what others might suggest.

I normally develop with Oracle in which sequences can be created and
associated with a table via a "trigger" on or after inserting a record.
The nice thing about this is that you can set at any time how the
sequence advances, ie, by 1, or by any number (2, 4, 10, 1000, etc, etc,
even reverse order with negative numbers).

However, in my situation I mentioned at the first para above, I was
desperate for some kind of in order sequence number for records in a Jet
application and didn't want to rely on date() or now() as these depend
upon the user's clock setting, which could potentially be screwed up.
Autonumber was the other alternative, so I did the above mentioned
search to make sure it was OK. Apparantly it is not.

What I ended up doing was relying on a default value for a form text box
control for the sequence number or a function which used the domain max
function +1

dmax("FieldName ", "TableName" ) +1

for the next number in sequence. This was for a single user application
(ie, not multiple front ends). I'm not sure how this would work with
multiple front end users. I would assume OK.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Vladislav]

Thanks for message.

As to how to create a unique identifier for a record in MS Access - this
problem I have identified and solved using simple function returning
the maximal value of IdVar in question over a set of exisitng records.
After that increment is a parameter, I am fully happy with 1.
The current problem came with autonumber variable Id which is used for
some internal references, like lines in combo box which by clicking
call the complete set of variables in the rest of a form. And it is not
so interesting how it could be done in Oracle ( I know simple and
perfect way to do this in SAS), but the task is to find solution for MS
Access, 2000+ (in Access97 I never encountered such a problem). It seems
clear bug, such a behavior of autonumber, when no replication is used,
just very plain and straightforward use of basic declared features of
Access 2000.

[Trevor Best]

Vladislav wrote:

Thanks for message.

As to how to create a unique identifier for a record in MS Access - this
problem I have identified and solved using simple function returning
the maximal value of IdVar in question over a set of exisitng records.
After that increment is a parameter, I am fully happy with 1.
The current problem came with autonumber variable Id which is used for
some internal references, like lines in combo box which by clicking
call the complete set of variables in the rest of a form. And it is not
so interesting how it could be done in Oracle ( I know simple and
perfect way to do this in SAS), but the task is to find solution for MS
Access, 2000+ (in Access97 I never encountered such a problem). It seems
clear bug, such a behavior of autonumber, when no replication is used,
just very plain and straightforward use of basic declared features of
Access 2000.

I found this in Access 2000 as well, and stayed with 97 until 2002 came
out. I don't know if it's been resolved, have you got all the service
packs installed?

--
This sig left intentionally blank

[Vladislav]

Trevor Best wrote:

I found this in Access 2000 as well, and stayed with 97 until 2002 came
out. I don't know if it's been resolved, have you got all the service
packs installed?

I have installed the whole package 2000. I have to stay with it, since I
am running the centralzied data collection in a multicentral study by
developing and sending to the data providing centres MS Access product
for local data entry and project management.