Setting Autonumber staring # - seems like it shoudl be easy?!

[allen_jr]

Hi
No one (or Access help) seems to ever actually say how to set the number for autonumbering. I have a list of records that are "members" and I want to add a new column (and make it primary) and assign everyone a MemberID number, but I want to start the numbers from 412403111, or something just as random - not "Member # 1".

Maybe this is so easy I'm missing the point, just can't find a simple way to set the number.... this would seem like a common function to me, but I just cant find any info... I am new to Access and don't really know how to do VBA, or even what that is

Thanks so much!!

Allen

 

[dkinley]

Does this help?

http://database.ittoolbox.com/group...-l/changing-autonumber-field-start-value-3457


-dK

 

[Banana]

Well... if you want to use this for another purpose (e.g. numbering a member), I think autonumber may not be a appropriate solution, especially if you were expecting sequentiality which it doesn't guarantees, only uniqueness.

Just make it a Long and either use that as key (which I think would be spotty, especially if member can have their ID changed) or a separate autonumber that's hidden from users and is for internal use only.

 

[allen_jr]

Thanks!! Finally found this, which helped me figure it out:



Overview: Changing Initial Value of an AutoNumber Field

In order to force Microsoft Access to number an AutoNumber field with a number you choose, follow these general steps (a more detailed example is shown later in this article):

1. Copy the design of the original table in which you want to set the starting AutoNumber value to a new table.
2. Change the AutoNumber field in the new table to a Number field with a FieldSize property of Long Integer.
3. Add a record to the new table, and set the Number field to a value that is one less than the starting number that you want for your original table. For example, if you want the AutoNumber field to start at 100, type 99 in the Number field of the new table.
4. Use an append query to add this new record to your original table. This action forces Microsoft Access to number any new AutoNumber fields with your number plus 1.

NOTE: Do not compact the database before you add a new record to the original table. If you do, Microsoft Access will reset the AutoNumber field value to the number 1. Back to the top
Example: Changing the Initial Value of an AutoNumber Field to 100

The following example uses the sample database Northwind.mdb to set a new starting value for an AutoNumber field in the Employees table:

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Open the sample database Northwind.mdb.
2. In the Database window, click the Employees table, and then on the Edit menu, click Copy.
3. On the Edit menu, click Paste.
4. In the Paste Table As dialog box, type EmployeesTemp in the Table Name box, and then click Structure Only. Click OK.
5. Open the EmployeesTemp table in Design view.
6. Change the data type of the EmployeeID field to Number, and set its FieldSize property to Long Integer.
7. Save the EmployeesTemp table and close it.
8. Open the EmployeesTemp table in Datasheet view and add the following new record: Employee ID: 99
   Last Name: Type your last name
   First Name: Type your first name
   Birth Date: Type your birth date
9. Create a new query in Design view based on the EmployeesTemp table.
10. Add all fields from the table to the query grid by double-clicking the * in the EmployeesTemp field list.
11. On the Query menu, click Append Query.
12. Select Employees in the Append To Table Name box, and then click OK.
13. On the Query menu, click Run. Microsoft Access displays the following message: You are about to append 1 row(s).
    Click Yes to append the record to the Employees table.
14. Close the query and do not save it.
15. Delete the EmployeesTemp table, and then delete the newly appended record from the Employees table.
16. Open the Employees table in Datasheet view and add a new record. Note that the Employee ID field starts numbering at 100.

 

[dkinley]

Awesome .. thanks so much for posting final resolution like that!

-dK