Autonumber Fields Resetting

[tarpon_zeke]

I have a table with an autonumber field as the key. One
of the functions the program has is to move a record from
this table to a "historical" table.

For some reason at one of my clients that runs this
software, the autonumber resets when they do this.

For instance: if there are 50 records in the table with
#'s 1-50 and they perform the move on the record with the
autonumber of 35, the next record they insert in the table
will have a # 35 in the autonumber, not a 51 as expected.

Any ideas on this? I can't recreate it on my computer or
at other clients. Very strange happening though and it is
messing up some other parts of the application. I rewrote
how the application assigns this # as a fix but I can't
figure out why it is doing this.

I have seen it happen in one other commercial software as
well but it is the first time it happened to me with my
applications.

Thanks in advance for any help.

PZ

 

[Luiz Cláudio]

Hi,

when you "move" these records, do you delete them from the original table or
only copy them to the new table?

If you delete from the original table and then compact the database, the
autonumber field will start from your last record + 1.

Luiz Cláudio C. V. Rocha
São Paulo - Brazil

 

[tarpon_zeke]

I do delete them but I do not compact and repair the
database. Even if I do compact and repair the database
the next counter # would be 1 greater than the last # in
the table. This is happening with data in the middle of
the sequence of #'s in the table.

I have records with autonumber 1-50. I copy the record
with auto # 25 to a history table and delete 25 from the
life table.

I insert a new record and the new record has 25 as the
autonumber.

 

[Luiz Cláudio]

Hi Tarpon,

when the database is compacted, autonumber is reseted (last record + 1),
otherwise it should not be affected (check if 'Compact on close' option is
not selected).

Anyway, if this field is meaningful, it may be a good idea to create a
second field (Long Integer) to control the ID, as Autonumber is
automatically filled and cannot be controlled.

Luiz Cláudio C. V. Rocha
São Paulo - Brazil


"tarpon_zeke" <[email protected]> escreveu na mensagem
I do delete them but I do not compact and repair the
database. Even if I do compact and repair the database
the next counter # would be 1 greater than the last # in
the table. This is happening with data in the middle of
the sequence of #'s in the table.

I have records with autonumber 1-50. I copy the record
with auto # 25 to a history table and delete 25 from the
life table.

I insert a new record and the new record has 25 as the
autonumber.