Primary Key need to be reconfigured to AutoNumber without changing the Field Data Typ

[lonlyspartakos]

Here is the thing guys :)
I started a access DB with more than 700 hundred records.
I have CusID Field which is a primary Key in VIPsInfo table and foreign Key in other tables.
My problem now is i made the CusID data type (Short Text) and i used to enter it manually.
I am looking for changing my manual entry to an Auto numbering or auto filling .
i know it was a stupide thing for me to do it at the first place and foolish thing to hope it can be fixed after 700 hundred records.
one more thing my CusID sequence is "CUS0001,CUS000 2...CUS0700..."
i think this is everything :D

[NeoPa]

An AutoNumber field contains Long Integers.

It is not even possible to hold text values, such as the ones you mention, in such a field.

[jforbes]

NeoPa is right, AutoNumber isn't going to work for you.

You could leave it as Text and then use a routine to return the next Key for the Default Value of the field on a Form. There is an example of one here: http://bytes.com/topic/access/answer...mber-data-type

[Carl Witte]

Ok. This is probably silly as the other two guys who have commented know WAY more than I do. But here are two ideas, if I understand your goal correctly.

Both involve adding an autonumber field and then removing the now redundant short text field.

You could use a standard prefix "CUST" and then the autonumber field and eliminate the short text entirely. This doesn't change the underlying data type (long) but just puts your text in front. Here is an old example. http://www.databasedev.co.uk/add_prefix.html

You could work only on a form and make a label control with the appropriate information formatting. Again, your underlying field remains long and you just read from the autonumber and change the display. (This is another version of what jforbes has suggested.)

You would lose your data you have entered in the CusID field. But it would be replaced by the new autonumber field.

[zmbd]

lonlyspartakos, I feel your pain.
In the dark past I've done such a thing, just to have a similar event happen. Lesson I learned here, don't use the primary key for anything other than as a unique record ID.

The issue with the article given in CW post (#4) is that the underlying autonumber is the stored value - hiding the true data, IMHO, not best practice.

To clarify.
In the article cited, for the autnumber field, the ["EMP"0000] format is given. The resulting displayed value in the table is EMP0001, EMP0002, EMP0003,... EMP9998, EMP9999... EMP(maxLng); however, the actual value stored is 1, 2, 3, ... 9998, 9999 ... MaxLng. Of course, when you click in the field, the EMP0001 becomes 0001; however, that may not be enough of a clue to for the unwary as to the true nature of the stored data. Thus, the unwary may attempt a query on [ID]="EMP0001" resulting in a data type mismatch error, when they should be building the query as [ID]=1. (Or in VBA, this will create the same error if one is trying to build on the perceived "String" when in fact the field value is the numeric portion only)

Jforb's link is certainly one option and is along the same lines I've used in the past. I would take his suggestion one step further by inserting a new field into the data table, if needed, set the indexed to "Yes No Duplicates" to store your new customer id then revert your primary key back to the numeric only.