How to change an Autonumber value in a field

[neelsfer]

I need to change some autonumbers field values in a table.
table = tblfacility
autonumber field = facilityID

ie
old FacilityID autonumber value = 776
new FacilityID autonumber value = 806
I do not want to reset the autonumbers starting at "1" again.
Is it possible using vba to do it?

[zmbd]

Not normally something that is done...
Try this:
BACK-UP the database!
Make a copy the table (I would make 2 copies even with the back-up), change one the copies autonumber field to plan numeric, make the change, delete all records from the origininal table, then append the updated copy back to the origininal table.

IN this case, I would recommend adding a field that has your "FacilityID " as some other name like "Facility_L oc" etc... I do this with my labdata, the autonumber is the primary key for the record; however, I have a "lotid" field. It used to mirror the autonumber, now it has an alphanumeric.
For many years now, do not use the autonumber field for anything except as a unique record key as I've been "burned" by the change in "vision" of the "powers that be" or a customer's need (etc...) one too many times!

[neelsfer]

Thx zmbd i will do that
i have an application that is going to be used at an additional 100 clinics to manage the inventory, and some of the facility names with their details and data, needs to be merged into one database. Nightmare stuff as some of the id's are duplicated on different pc's etc etc.

[zmbd]

The newest versions of Access can use the Replication ID in the Autonumber field:

Globally unique identifier (GUID)
(GUID: A 16-byte field used in an Access database to establish a unique identifier for replication. GUIDs are used to identify replicas, replica sets, tables, records, and other objects. In an Access database, GUIDs are referred to as Replication IDs.) N/A 16 bytes

It looks like the license key for so many pieces of software out there.