Autonumber problem

[KimTong via AccessMonster.com]

Hi,

I have a problem with autonumber field in my project: when I deleted some
last records, it seems MS Access saved the last number of the autonumber. So
when I added a new record, the number would be skipped from the previous
record.

I'd like the next record will be in order from the previous record in my
outonumber field. Is anybody can tell me how to solve this problem?. Thanks...



KT

 

[Jason Lepack]

If the value of the autonumber actually matters then you shouldn't be
using atuonumber. Populate it through other means.

 

[Guest]

Autonumbers don't work that way. To get what you want, you'll need to write
some code to check the largest number in the table then add one to it. For
this to work, you'll need to use a form. It can also be a problem if there
are more than one user adding records at a time.

 

[KimTong via AccessMonster.com]

OK, so noway to reset the autonumber. Thank you for all your suggestions.

KT

Autonumbers don't work that way. To get what you want, you'll need to write
some code to check the largest number in the table then add one to it. For
this to work, you'll need to use a form. It can also be a problem if there
are more than one user adding records at a time.

Hi,

[quoted text clipped - 7 lines]

KT

 

[John W. Vinson]

Hi,

I have a problem with autonumber field in my project: when I deleted some
last records, it seems MS Access saved the last number of the autonumber. So
when I added a new record, the number would be skipped from the previous
record.

This is exactly how autonumbers are designed to work. They are not a record
count; they are not guaranteed to be sequential; they can even become random.
Typically they are not suitable for human consumption - they are a "behind the
scenes" unique key useful for linking tables together, and that's ALL that
they are!

I'd like the next record will be in order from the previous record in my
outonumber field. Is anybody can tell me how to solve this problem?. Thanks...

You can do this programmatically but... it's difficult. Let me post some
hypothetical questions:

If you have 31227 records in the table and delete item 3, do you want to
renumber 31223 records in this table, in all related tables, on all the
printouts that have ever been generated, and in all the minds holding on to
ID#4487 or ID#30225? Or do you want to leave a gap? Or do you want the next
record after 31227 to be 3, and the next record after that to be 31228?


John W. Vinson [MVP]

 

[Guest]

Try compacting the database, it may work.

OK, so noway to reset the autonumber. Thank you for all your suggestions.

KT

Autonumbers don't work that way. To get what you want, you'll need to write
some code to check the largest number in the table then add one to it. For
this to work, you'll need to use a form. It can also be a problem if there
are more than one user adding records at a time.

Hi,

[quoted text clipped - 7 lines]

KT

 

[John W. Vinson]

Try compacting the database, it may work.

Only with some older versions of Access.

John W. Vinson [MVP]