change or delete ID

[msdobe]

I made the mistake of changing the ID data type from autoformat to text. How
do I change it back? I tried to change but I can't because I was told once it
changes to text it can't go back to autoformat. Then I tried to add another
ID and can't do that because I already have one. What can I do?

Thanks!
Jenny

 

[Douglas J. Steele]

Do you mean "AutoNumber" rather than "autoformat"?

You cannot change a field to AutoNumber once there's data in the table. I'm
not sure I understand what you mean that you "to add another ID and can't do
that because I already have one".

 

[msdobe]

I meant autonumber instead of autoformat. I tried to add another ID (to keep
track of the number in the database) and I can't because I'm told I already
have one.
Are you saying that an ID cannot be added once there's data in a table? If
so, can I copy this data and make another new table?

 

[Douglas J. Steele]

No, I'm saying that an existing field cannot be changed to an Autonumber
once there's data in the table (whether or not there's data in the field
you're trying to change). You should be able to add a new field and make it
an Autonumber, though.

What's the actual error message you're getting when you try adding a new
Autonumber field? You're limited to having a single Autonumber field in any
table.

 

[Ken Sheridan]

Jenny:

What you can do is create a new table under a different name with the same
columns as the original, but with an autonumber column as the one you changed
to text. In your original table change the data type of the text column to a
normal long integer number. Then create an append query which inserts all
the rows from your original table into the new one. Make sure the data has
been inserted correctly and, if so, delete any relationships of which the
original table is a part, and then delete the original table, rename the new
one to the original's name and recreate any relationships with other tables.

Ken Sheridan
Stafford, England

 

[msdobe]

The message says 'you already have a field named 'ID'

 

[Douglas J. Steele]

Give the new field a different name (or rename the existing field)

 

[Ken Sheridan]

Jenny:

What I should have made clear is that this preserves the original values of
the field in the new autonumber field, whereas adding a new autonumber field
to an existing table will populate it with arbitrary sequential values.

Ken Sheridan
Stafford, England

 

[msdobe]

Thanks, Ken. I did it. (I sure won't make that mistake again!!)

Really appreciate everyone's help!
Jenny