Turning sequential numbers into an Auto Number Field

[Guest]

We converted an existing list in a Word Table into an Access Table. The
conversion process went well but now we would like the current Order Number
field to be our Primary Key and we want it as an AutoNumber field. We want
the sequential order number entered when we create a new record. When I try
to change the field type to AutoNumber, I receive an error that says you
cannot convert the current field to AutoNumber. Since Autonumber won't work,
is there another way to make this work?

 

[Ken Snell [MVP]]

Create a new table that is a duplicate structure of the current one, but add
a new field to the table, and make it the autonumber field.

Then write an append query that will copy the data from the original table
to the new table, except use the sequential number field as the source for
the autonumber field.

That will populate the autonumber field with the current values.

You then can do the necessary steps to either make the new table replace the
old table, or to delete data from the old table and add the autonumber field
and then append the data back to it from the new table, or many other
possibilities (depending upon your database design).

 

[Joseph Meehan]

We converted an existing list in a Word Table into an Access Table.
The conversion process went well but now we would like the current
Order Number field to be our Primary Key and we want it as an
AutoNumber field. We want the sequential order number entered when
we create a new record. When I try to change the field type to
AutoNumber, I receive an error that says you cannot convert the
current field to AutoNumber. Since Autonumber won't work, is there
another way to make this work?

I suggest you may not want to use Autonumber for that use. Autonumbers
are designed to provide unique numbers. It in not designed to provide
numbers in order and for a number of reasons may not do so. As a result
using them in any application where the user sees the numbers is likely to
end up with confusion.

There are other ways of providing the numbers you want depending on the
particual application.