autoincrement

[thierno]

Dears members,

Is there a way in MS Access to keep an autoincrment field counting successivly after deleting and inserting? I want the field having the right counting.

Thanks in advance for your help

 

[yhgtbfk]

Dears members,

Is there a way in MS Access to keep an autoincrment field counting successivly after deleting and inserting? I want the field having the right counting.

Thanks in advance for your help

I am pretty sure there is no way. I read on the Microsoft site somewhere a solution that was a messy solution that they didnt back entirely.

I guess one way would be to do your own auto number system using visual basic coding, and adjust the numbers with some sort of a loop as needed.

 

[DALIEN51]

I Would........

I would create a query based on the table listing all of the record numbers in ascending order.

Change the autonumber field concerned to a number, long field.

When writing a record:

DIM MYDB AS DATABASE
DIM SOURCE AS RECORDSET
DIM TARGET AS RECORDSET

SET MYDB = CURRENTDB()
SET SOURCE = MYDB.OPENRECORDSET("{newly created query}")
SET TARGET = MYDB.OPENRECORDSET("{table holding record number}")

SOURCE.MOVELAST

TARGET.ADDNEW
TARGET![{record number field}] = SOURCE![{record number field}] + 1
TARGET.UPDATE