AutoNumber correlate with Number?

  • Thread starter Thread starter MikeF
  • Start date Start date
M

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
 
MikeF said:
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.]
Click to expand...


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.
 
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
 
Back
Top