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?
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_Loc" 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!
3  6529  zmbd 5,501
Expert Mod 4TB
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_Loc" 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!
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 5,501
Expert Mod 4TB
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Roger Withnell |
last post by:
I'm inserting a new record into an MS SQL database table
and I want to obtain the new records autonumber
immediately afterwards, as follows:
MadminRS.CursorLocation = adUseServer...
|
by: Matt Herson |
last post by:
I have been trying to find a way to use JavaScript to change the value of a
hidden field on submit. I am already invoking a JavaScript to handle the
validation on submit.
The reason I need to...
|
by: Rick |
last post by:
I have an XML document that is generated from Infopath, I need to change
the value of a namespace that is defined in a node in the form:
<xsf:xDocumentClass "xmlns:my=valuehere">.
when i navigate...
|
by: Galina |
last post by:
Hello
I have an application in MS Access 2000. I have a form, which includes
a subform. The subform is based on a table, but locked for any
editing. There is Edit button. When clicked, it starts a...
|
by: DHRRush |
last post by:
I have a simple complaint tracking Db with one main table and one main
form for entering data. I need for the users to be able to see the
record number for future reference when they come back to...
|
by: Rex |
last post by:
Hi,
I want to change a value in one table depending on the value(s) in
another table. I am trying to achieve this in a form.
to elaborate
I have a many-to-many relationship between tables...
|
by: FSUKXAZ |
last post by:
I'm trying to change Identity on field w/Data using SQL Manager and cannot. The table is called Members and the field is called MemberID. It is already the Primary key, Not Null, and Unique...
|
by: Ciaran |
last post by:
Hi anyone know how to change or even just clear the value of a file
input field using javascript?
document.getElementById('fieldname').value='newvalue';
doesnt work as you would expect.
...
|
by: Wayne |
last post by:
I'm using the following SQL statement to find the next highest
autonumber value in a table where "CDUGActID is the autonumber field
in the "CDUGActuals" table:
SELECT CDUGActuals.CDUGActID,...
|
by: jaad |
last post by:
how do you reference a single value field to a multi-value field?
I sometime use a macro in form1 to open form 2 containing the same ID
example:
Open form: WorkOrder
where condition: ="="...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
