Problem with AutoNumber

My problem is concerning the autonumber
feature...particularly in a table...My tables did have the
autonumber feature enabled, then had some corruption with
the whole database.

I was able to recover the tables and all records, however,
the autonumber feature had be changed to number. Since I
wasn't able to just change the number field back to
autonumber, I went in and filled all the non-sequential
records with blank records (ex.: 1,2,4,5 - I put in a 3
with a blank record to fill the void to finish the
sequence). Now, I want to put an autonumber field back in
to be able to autonumber my tables.

Here is the problem...when I add a new autonumber field,
it resorts all the blank records to the bottom of the
table. Even if I sort ascending. Any suggestions out
there?

"accessmonk" wrote in
:


Here is the problem...when I add a new autonumber field,
it resorts all the blank records to the bottom of the
table. Even if I sort ascending. Any suggestions out
there?

You only get sorting when you sort the table... if you sort by a column
that has blanks, then those blanks all move to the bottom.

To get back to your original problem, the way to convert a LongInteger
field back to an Autonumber is this:

1. Back up everything twice...

2. make a new empty table with all the fields as you want them,
including substituting the old number field with an AN.

3. append the old table into the new one. Make sure that the old
number field gets put into the new AN column -- for example

INSERT INTO NewTable (ANCounter, FName, DtOfBirth)
SELECT OldIDField, FName, DtOfBirth
FROM OldTable

This creates an exact copy, with the same numbers in the AN column as
you started with. The new AN counter will carry on from the highest
value of the OldIDField plus one, as you would expect.

4. Delete all the relationships

5. Rename the old table, rename the new table to the old table name,
then recreate the relationships.

6. After fully testing everything, you can delete the old table. Or
resort to your backups!

Hope that helps



Tim F

-----Original Message-----
"accessmonk" wrote
in
:


Here is the problem...when I add a new autonumber
field,
it resorts all the blank records to the bottom of the
table. Even if I sort ascending. Any suggestions out
there?

You only get sorting when you sort the table... if you
sort by a column
that has blanks, then those blanks all move to the bottom.

To get back to your original problem, the way to convert
a LongInteger
field back to an Autonumber is this:

1. Back up everything twice...

2. make a new empty table with all the fields as you want
them,
including substituting the old number field with an AN.

3. append the old table into the new one. Make sure that
the old
number field gets put into the new AN column -- for
example

INSERT INTO NewTable (ANCounter, FName, DtOfBirth)
SELECT OldIDField, FName, DtOfBirth
FROM OldTable

This creates an exact copy, with the same numbers in
the AN column as
you started with. The new AN counter will carry on
from the highest
value of the OldIDField plus one, as you would expect.

4. Delete all the relationships

5. Rename the old table, rename the new table to the old
table name,
then recreate the relationships.

6. After fully testing everything, you can delete the old
table. Or
resort to your backups!

Hope that helps



Tim F

.


That is just what I needed. Worked like a charm. Thanks a
million, Tim.