AutoNumber correlate with Number?

[MikeF]

In tblCompanies, CompanyID is a "Number" field, non-primary key.
I changed it to this temporarily, as it was being rebuilt in a new db, and
all the old Access2002 id's were replicated, so record #524 had a CompanyID
of
-21118374650238347.

At any rate, it's now in Access2007, all links/lookups are restored to the
CompanyID field, which is numbered 1 to 1000 [this example contains 1000
records].

So, thought it would be easy to change the CompanyID from Number to
AutoNumber w/PK.
Changed CompanyID field to CompanyIDx, sorted ascending, order on load.
Added CompanyID, AutoNumber w/PK.

It comes up in a completely different sort order.
Ie record 1 from CompanyIDx is record 626 in CompanyID, and so on.
Really need the numbers to be equal/correlate in each field.
[Then I can delete the CompanyIDx field and the table is done.]

So far no luck at all after a dozen different attempts.
Can anyone assist?

Thanx in advance.
- Mike

 

[Marshall Barton]

In tblCompanies, CompanyID is a "Number" field, non-primary key.
I changed it to this temporarily, as it was being rebuilt in a new db, and
all the old Access2002 id's were replicated, so record #524 had a CompanyID
of
-21118374650238347.

At any rate, it's now in Access2007, all links/lookups are restored to the
CompanyID field, which is numbered 1 to 1000 [this example contains 1000
records].

So, thought it would be easy to change the CompanyID from Number to
AutoNumber w/PK.
Changed CompanyID field to CompanyIDx, sorted ascending, order on load.
Added CompanyID, AutoNumber w/PK.

It comes up in a completely different sort order.
Ie record 1 from CompanyIDx is record 626 in CompanyID, and so on.
Really need the numbers to be equal/correlate in each field.
[Then I can delete the CompanyIDx field and the table is done.]

Autonumbers are not guaranteed to be anything except unique.
If you want to apply any meaning to an autonumber field OR
if users can see the autonumber, then you should not be
using autonumber.

 

[JimBurke via AccessMonster.com]

I'm not certain I understand what you were trying to do. Are you saying that
CompanyID had been an AutoNumber field and you changed it to a Long field
instead, and now you want to change it back to an AutoNumber field and
somehow keep the old values which are in the Long field? When you say the
table was being rebuilt in a new DB, did you still want to retain the old
copy of tblCompanies? If you bulit a new DB but wanted the same tblCompanies
table, why woudn't you just import it into the new DB? I'm confused as to
exactly what you were trying to accomplish.

In tblCompanies, CompanyID is a "Number" field, non-primary key.
I changed it to this temporarily, as it was being rebuilt in a new db, and
all the old Access2002 id's were replicated, so record #524 had a CompanyID
of
-21118374650238347.

At any rate, it's now in Access2007, all links/lookups are restored to the
CompanyID field, which is numbered 1 to 1000 [this example contains 1000
records].

So, thought it would be easy to change the CompanyID from Number to
AutoNumber w/PK.
Changed CompanyID field to CompanyIDx, sorted ascending, order on load.
Added CompanyID, AutoNumber w/PK.

It comes up in a completely different sort order.
Ie record 1 from CompanyIDx is record 626 in CompanyID, and so on.
Really need the numbers to be equal/correlate in each field.
[Then I can delete the CompanyIDx field and the table is done.]

So far no luck at all after a dozen different attempts.
Can anyone assist?

Thanx in advance.
- Mike

 

[dymondjack]

There is a way to reset the autonumber seed so it starts on specified number
for you. Go to allenbrowne.com/tips and look for his articles on autonumbers
if you want to try it.

I have to agree with Mashall though... users should never see an autonumber
value and should never be trusted as anything more than a unique value. In
most cases, the actual value of an autonumber shouldn't be the concern of the
programmer, just a number for the application to grab data. If you need a
field to have a purpose to your user see about making a separate field just
for that.

Anyway if you really need to you can see Allen's article on resetting the
autonumber, it should help here.

--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill