Autonumber to table changes the order of client list, Why?

[kazurin]

I already have a prettly lon list of clients and I'm having trouble inserting auto number like I want, since they are all already in order archived in file cabinets.

--THIS IS WHAT I HAVE---------

Number Tracking Client
0139 01 John Doe
0140 02 Jane Smith
0141 03 Mary Jane
0001 04 Susy Lin
0002 05 Karen Jones
0003 06 Rick M

I want the number to correspond to tracking, but then delete tracking and rename number to Tracking
to end up like the following

----THIS IS WHAT I WANT-----

Tracking Client
0001 John Doe
0002 Jane Smith
0003 Mary Jane
0004 Susy Lin

Any suggestions as of why this is happening????

 

[Remou]

Autonumbers are just numbers, they do not correspond to Tracking or anything else, they are sometimes negative and sometimes they jump. They are only intended to be unique. What you need is a number with meaning, not an autonumber, which has no meaning.

 

[ProgramError]

well stated Remou!

I have a legacy headache caused buy the original user using autonumbers as a reference ID. If I had known this when I first started learning access and VBA, I would have changed it for a separate recordnumber system back then.


Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[Golom]

There are several threads that discuss the problems with autonumbers. To summarize

An autonumber's only virtue is that it is unique (and even than may not always be true.)

Gaps will appear in the autonumber sequence if you begin the creation of a new record and then cancel it or if you delete a record. New autonumbers will not fill those gaps.

You cannot set the values in an autonumber field. Access manages creating and entering new autonumbers.

If you redefine an autonumber field after the table contains data then you can create gaps and/or duplicate autonumbers in the sequence.

Adding an autonumber field to a table that already contains data (as you are doing) provides no guarantee that those numbers will be assigned in any particular order (for example input order or primary key order.)

Think of them as a license plate on a car. They tell you nothing about the vehicle (i.e. the record) to which they are attached. The only significance that you can make of them is that no other car has that licence plate.

 

[kazurin]

Wow so much things to learn, so i need to make a number with meaning, which wont be automatically assign right??

Or what other alternative do you guys suggest>

Thanks for the input!