Hi Anna

First, if you want your customer id values in order with no gaps, then an
autonumber is NOT the way to go. Autonumbers are not really intended for
human consumption. They might as well be (and in fact can be) completely
random. Even with incremental autonumbers, you can get gaps when a record
is created but is then cancelled without being saved.

However, is you do want to add an autonumber primary key to an existing
table, here is how:

1. VERY IMPORTANT!! Before you start, make a copy of your database file in
case you mess up. Also, ensure that the Name Autocorrect option is turned
of.

2. Open your table in design view and add a new field with the same name as
the old primary key field, and with data type Autonumber. Rename the old
field to "OldPK" but do not change the PK yet. Save the table. When you
open it you should see the new field populated.

Repeat the following steps (3-5) for EACH related table:

3. Open the related table and add a new field with the same name as the old
foreign key field in the relationship, and with data type numeric. Set its
Size property to Long and delete the "0" from the DefaultValue property.
Rename the old FK field to "OldFK" and save the table.

4. Create a query with the two tables (they should automatically join on the
OldPK and OldFK fields). Change the query to an update query and add the
new FK field to the grid. In the "Update to" cell, enter the fully
qualified name of the new PK field - e.g. [tblCustomers].[CustomerID]. Run
the query.

5. Open the related table and check that the new FK field has been
populated. You don't need to save the query.

6. Once all the related tables have been dealt with, open the relationships
window and delete the relationships between the main table and each related
table.

7. Open the main table in design view and change the PK to the new field.
Then delete the OldPK field. Save the table.

8. Open the relationships window again and create a new relationship between
the new PK field and each of the new FK fields.

9. Open each of the related tables and delete the OldFK field.

All of your old queries/forms/reports should function as before, except of
course you won't be able to edit the new customer id field.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"A.P." wrote in message
...
Hi Guys!

I have a questions. In my database there is a field called customer id. it
is present in two tables and used to build a relationship one -many
between
them. A field type is number so you have to enter it manually. I messed up
as
I added a number outside the numeric sequence and followed it up. So
instead
of 89, I added 99. I want to correct it by adding a new field with
autonumber. In this way, I would not mess up as my database creates new
numbers for me. However, I am a bit afraid that by adding a new field and
changing relationship I will destroy the balance between tables and make
wrong records joined. How can I avoid it??

Thank you for your time and help

Anna