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.

 

[Pat Hartman]

You are confusing a unique identifier with a record number. Old ISAM files and spreadsheets have record numbers, relational databases do not. An autonumber is used as a unique identifier for a row in a table. It has NO other meaning.

If you want to create your own recordnumber, you're in for some tricky code. You would need to reassign the record number any time some row was deleted in order to fill the gap. This is truely a waste of time and computing resources.

 

[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