auto number fields

[gwboolean]

Can the starting number be defined by the user in an autonumber fields. i.e. start as say 1000 instead of 1?

[ItsMe]

Yes. Make a copy of everything. Then, delete all of the records from the table you wish to assign the start number. Now, do a compact and repair. The C and R will reset the Autonumber.

Copy your table via Right Click and paste directly into the Navigation Pane. Open you new table in design view. Change the Autonumber to Long Integer. Save your table. Open your table in Datasheet View and create a new record. Type 999 or 998 or 997 into the PK that is now type Long. Add data to the required fields, adding data to at least one of the many columns.

Now you need to append to your table that has no records and the Autonumber. Create a query and change the type to Append. Drag the Copy table onto the Designer Surface. Select the necessary columns and add them to the Design Grid. Run your query.

[gwboolean]

ItsMe,

thanks. The table I have is new and has no records yet. How would I do it for no records yet?

[ItsMe]

Same way. The idea of deleting records and doing a compact and repair is to reset the counter for Autonumber. So, if the latest PK in your Autonumber was 52452, it is likely you will not be able to append a record to that table with a value less than 52452

[ssanfu]

Have Autonumber field start from a value different from 1
http://access.mvps.org/access/tables/tbl0005.htm

But why would you want to do this?

You might want to read this:
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

[gwboolean]

What I have in mind ssanfu is this. This is a database that I previously had and in which I was using a 10 character smart numbering system for the identity of items, documents, etc. It has taken me a long time to get over the use of smart numbers, but I am over it and ready to move on. So all I now need is a unique incrementing number for the id number of all of the previously mentioned table items/records. However, I want to start at 1000000 instead of 1. If I could do that with a auto number field that would be ideal.

ItsMe,

I will see if I can work through that. Thanks.

[ssanfu]

OK.....

Another option: you could always use a custom autonumber field (ie roll your own numbers)

See BaldyWeb.com http://baldyweb.com/CustomAutonumber.htm

[orange]

Just curious about this

However, I want to start at 1000000 instead of 1.

What's the rationale?

[gwboolean]

The rational is based on a need to have a 8 character/digit part/Item number for each item added to table. I wanted to start with 10000000 because I wanted to skip through all values below that. The reason being that for most part/item numbers no one wants values like 00000001.