Asked By
Jake Ross
220 points
N/A
Posted on - 06/21/2011
Hi everyone,
I am working with a program and I used MS Access 2003 as a back-end for my database. I have a field “Item no” and its data type is Auto Number. I want to start the numbering with 1000, so that the next number in auto number will be 1001, 1002 and so on. But the default value is 1.
How can I change this value? Kindly assist me with this one. Thanks for your answers.
Changing the starting value of auto number in MS Access
You can do this by creating another table that you will add to the database that you are using by Append Query. This method adds records of the current table that you are working in to another table.
If you want to add a database table to another database, you must know the name and the exact location of it. If you have an existing database then you will need to create a new database table that you will append to the existing record.
Here are the steps on how to change the starting number of the AutoNumber data type:
-
Create a new table that has the same field name to your existing database table where AutoNumber is set. For example, on your existing database, you have Item no field name, use this field name on the created table.
-
Set the data type of the new database table to “Text”.
-
Save the Table. It will ask you to set a primary key, just click “No” to avoid adding to the new table.
-
Under “Objects” click the “Queries”. Double click the “Create query in Design view”.
-
Choose either the existing database table or the new table that you created.
-
Double click the asterisk.
-
On the toolbar click the down arrow on the “Query Type”
and select “Append Query”.
-
Select the database table. If you choose the existing table on the previous step, then choose the new database table that you created.
-
Click the Run
on the toolbar to start appending of records. Save the query.
-
Check the table. If successful, you can now delete the created query and the other table.
Changing the starting value of auto number in MS Access
In case you are using a new table that contains no records at all, then you can change the starting value of the AutoNumber field that has its NewValues property, and then set it to Increment to a number other than 1. For the table that contains values already, use the procedure below to change the AutoNumber:
-
The first thing that you will need to do is to create a temporary table that has just one field, call it a Number field; and then you will set its FieldSize property to Long Integer, and thereafter you will give it the same name as the AutoNumber field that is on the table that you want to change its values.
-
Then you will go to the datasheet view, and there you will need to enter a value in the Number field of the temporary table which should be less than the starting value that you want for the AutoNumber field.
-
After that, you will need to create and run an append query, which will be used to append the temporary table to the table whose AutoNumber value you want to change.
-
Next you will need to delete the temporary table.
-
And then you will delete the record that has been added by the append query.
-
And then save the changes.
-Clair Charles
