Auto numbering

[tallos3]

I have a table counting by auto numbering. My last record is 1112.By mistake
I canceled 2 entries and now my next record has 1115 number although is the
1113th. How can I fix it and continue from my last higher valid number?

 

[Stefan Hoffmann]

hi,

I have a table counting by auto numbering. My last record is 1112.By mistake
I canceled 2 entries and now my next record has 1115 number although is the
1113th. How can I fix it and continue from my last higher valid number?

You can't, when you have an AutoIncrement.

The AutoIncrement only ensures that your next number is greater then the
actual and that the number is unique.
When you need a numbering without any gap, then you need to calcultate
your numbers by your own using

=Nz(DMax("numberField", "yourTable"), 0) + 1

in the Before Update event of your form.


mfG
--> stefan <--

 

[Krzysztof Pozorek [MVP]]

(...)

I have a table counting by auto numbering. My last record is 1112.By
mistake
I canceled 2 entries and now my next record has 1115 number although is
the
1113th. How can I fix it and continue from my last higher valid number?

In VBA code (module, immediate window) You may write something like this:
DoCmd.RunSQL "alter table Table1 alter column ID counter(1113,1)"


You can also use the query:
alter table Table1 alter column ID counter(1113,1)

But what you need to?

K.P. Poland MVP

www.access.vis.pl

 

[tallos3]

(...)


In VBA code (module, immediate window) You may write something like this:
DoCmd.RunSQL "alter table Table1 alter column ID counter(1113,1)"


You can also use the query:
alter table Table1 alter column ID counter(1113,1)

But what you need to?

K.P. Poland MVP

www.access.vis.pl



Here is the problem exactly:

I have a database with athletes. It's athlete the time he register takes a
unique number. I thought the best way was to have an auto numbering.
By that, I had a unique number for every athlete and I knew how many persons
are registered in the Federation.
So, the number of the record (in the bottom of the page) should be the same
with the registration number of each athlete.
But now the 1113th athlete takes 1115 as registration number because I
cancelled 2 records.
As you can understand, I need to continue my table and the next athlete must
take as reg. no. 1113
Or else I have to write again 1112 athletes with all the data (father's
name, birthday, residence, bla bla bla).
TOO MUCH DATA!!!

 

[Douglas J. Steele]

I have a database with athletes. It's athlete the time he register takes a
unique number. I thought the best way was to have an auto numbering.
By that, I had a unique number for every athlete and I knew how many
persons
are registered in the Federation.
So, the number of the record (in the bottom of the page) should be the
same
with the registration number of each athlete.
But now the 1113th athlete takes 1115 as registration number because I
cancelled 2 records.
As you can understand, I need to continue my table and the next athlete
must
take as reg. no. 1113
Or else I have to write again 1112 athletes with all the data (father's
name, birthday, residence, bla bla bla).
TOO MUCH DATA!!!

There's no need to reenter the existing data. Simply add a new Long Integer
field to the table, write an Update query that copies the Autonumber value
into that new field, then delete the Autonumber field.

 

[Jeff Boyce]

The simplest way to count the number of records in a table is to use a
Totals query and COUNT.

You don't need to (and there are good reasons not to) use an Autonumber for
this purpose.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[M. Murphy]

I had a similiar problem once, and found many ways to fix it.
This is my favorite way, you can set the number to start exactly where you
need it, and remember it will start 1 higher than the number you enter into
the new table, so if you need the next number to be 1116, set the value to
1115, and I don't know if this article mentions it, but run a compact/repair
before doing this.....

http://database.ittoolbox.com/group...-l/changing-autonumber-field-start-value-3457