autonumber incrementing with gaps once a record is deleted

[sam]

Hi All, Can I reset the auto number increment such that it has no gaps?
For eg, If i delete couple rows the auto numbering is not updated on its own..

it says..
1
2
3
6
9
.......


Please Help

Thanks in Advance

 

[Beetle]

The only purpose of Autonumber is to create a unique key
value for each record. It will not necessarily be sequential
(or even positive for that matter) and there will be gaps in
the sequence. It is not suitable for a sequential numbering
system. You will need to handle this programmatically with
something like;

Nz(DMax("[YourNumberField]", "tblYourTable"), 0) + 1

which could be done in a calculated field in a query, or in
code (i.e. in the Before Insert event of a form), etc.

 

[sam]

Thanks for your help Beetle.
I would like to add a new column in access that increments in an order, even
if i delete one record it resets the count and has no gaps. I am populating
this access table through an excel form, is there any way I can add a code to
my excel form where I can get number column like I want?

Thanks in Advance

The only purpose of Autonumber is to create a unique key
value for each record. It will not necessarily be sequential
(or even positive for that matter) and there will be gaps in
the sequence. It is not suitable for a sequential numbering
system. You will need to handle this programmatically with
something like;

Nz(DMax("[YourNumberField]", "tblYourTable"), 0) + 1

which could be done in a calculated field in a query, or in
code (i.e. in the Before Insert event of a form), etc.

--
_________

Sean Bailey

Hi All, Can I reset the auto number increment such that it has no gaps?
For eg, If i delete couple rows the auto numbering is not updated on its own..

it says..
1
2
3
6
9
......


Please Help

Thanks in Advance

 

[sam]

Thanks for your help Beetle.
I would like to add a new column in access that increments in an order, even
if i delete one record it resets the count and has no gaps. I am populating
this access table through an excel form, is there any way I can add a code to
my excel form where I can get number column like I want?

Thanks in Advance

The only purpose of Autonumber is to create a unique key
value for each record. It will not necessarily be sequential
(or even positive for that matter) and there will be gaps in
the sequence. It is not suitable for a sequential numbering
system. You will need to handle this programmatically with
something like;

Nz(DMax("[YourNumberField]", "tblYourTable"), 0) + 1

which could be done in a calculated field in a query, or in
code (i.e. in the Before Insert event of a form), etc.

--
_________

Sean Bailey

Hi All, Can I reset the auto number increment such that it has no gaps?
For eg, If i delete couple rows the auto numbering is not updated on its own..

it says..
1
2
3
6
9
......


Please Help

Thanks in Advance

 

[Beetle]

It sounds like you basically just want a row number, which
should *not* be done at the table level. Import the raw data
only into your table, then use a query (or a form) to view it.
At the link below, you can find some methods for adding a
row number column to a query.

http://www.lebans.com/rownumber.htm

--
_________

Sean Bailey

Thanks for your help Beetle.
I would like to add a new column in access that increments in an order, even
if i delete one record it resets the count and has no gaps. I am populating
this access table through an excel form, is there any way I can add a code to
my excel form where I can get number column like I want?

Thanks in Advance

The only purpose of Autonumber is to create a unique key
value for each record. It will not necessarily be sequential
(or even positive for that matter) and there will be gaps in
the sequence. It is not suitable for a sequential numbering
system. You will need to handle this programmatically with
something like;

Nz(DMax("[YourNumberField]", "tblYourTable"), 0) + 1

which could be done in a calculated field in a query, or in
code (i.e. in the Before Insert event of a form), etc.

--
_________

Sean Bailey

Hi All, Can I reset the auto number increment such that it has no gaps?
For eg, If i delete couple rows the auto numbering is not updated on its own..

it says..
1
2
3
6
9
......


Please Help

Thanks in Advance