<div>
glad to see you answering questions, Bing ~
</div>


<div>
<blockquote>
However the user wants that number to start from 1,000 instead.
</blockquote>
You need to redesign this. <br />
The AutoNumber is for you and the database and should never have any meaning. <br />
Order numbers are for humans and must created separately.<br />
<br />
Even if you fix the start number, you'll get in trouble when the user deletes an order and the AutoNumber gets lost. And numbers may change if the database should need a repair someday.<br />
<br />
/gustav
</div>


<div>
to correct a common misconception that &quot;Autonumbers are unique&quot;. &nbsp;In theory that is true. &nbsp;In reality, a table in only assured to have unique autonumbers if the autonumber field is a Primary Key or has a Unique Index.<br />
<br />
I can understand fixing the start number to keep the number of digits more consistent.<br />
<br />
... besides, incorporating ideas from others helps them get more involved in the project. &nbsp;In the end, what is more important? Where an autonumber starts or seeing a smile on someone who feels they made a great contribution?<br />
<br />
`
</div>


<div>
@crystal<br />
<br />
thanks for your greeting. nice to meet you online, a MVP for 10+ years!<br />
<br />
i believe we must had passed each other by somewhere at MVP summits in Redmond. :)
</div>


<div>
@BING<br />
&quot;better know what's the background or specific reason that makes the user have such a concern. e.g. do they need to do some kind of calculation on the AutoNumber field in the near future?&quot; ---&gt; &nbsp;I did not ask the reason why and they don't have any prior order system to convert from, so I don't think it is because of calculation reasons.<br />
<br />
I asked the user to re-consider the request and a moment ago, he said it was fine to start with 1.<br />
<br />
But I will try the suggestions made above and post the results.
</div>


<div>
@Nick, many of us who know have been bit or seen someone who has ... glad you won't have to go through the hard knocks ~ when I get a database from someone else, unique index on autonumbers is something I check for.<br />
<br />
@Bing you're welcome and thanks ~ perhaps we have ... what is your name?
</div>


<div>
@sglee<br />
<br />
I think you missed the point.<br />
It is irrelevant what the starting number is for an autonumber<br />
<b>because the autonumber is for you the developer and the database alone</b><br />
Making the sequence, value, and completeness of an autonumber field important to a user is going to be a fail-whale.<br />
<br />
Create an order number system that meets the users needs.<br />
Don't use an autonumber as its basis
</div>


<div>
even though autonumber values are irelevant as long as they are unique, the fact that someone needs to compare them to make sure data is going into the right places remains. &nbsp;That is easier to do when the number of digits is the same instead of, for instance, 17, 434 and 1198. &nbsp;It is also easier for customers if the autonumber is given significance. &nbsp;Many of the people I help with Access DO use autonumbers to mean something important sometimes too -- and that is fine, in my opinion.<br />
<br />
Numbers skip, of course -- that is ok. &nbsp;Perhaps the issue is more where to start and how many digits to have.
</div>


<div>
There are those topics that generate strong debate.<br />
This is one.<br />
Many folks feels strongly that an autonumber makes an excellent PK, that PKs should be numeric and have no significance to the user.<br />
I expose the PK to the user, but do not label it.<br />
The user knows that it should trend upward over time, that's it.<br />
<br />
<i> the fact that someone needs to compare them to make sure data is going into the right places remains.</i><br />
I have no idea what that means.<br />
Autonumber PKs and referential integrity mean that this isn't an issue in anyway that I can conceive.<br />
<br />
<i>It is also easier for customers if the autonumber is given significance.</i><br />
There'll be strong disagreement about that.<br />
Something that <i>looks like</i> a sequence should be continuous, and autonumbers are not.<br />
That confuses users.<br />
<br />
<i>Many of the people I help with Access DO use autonumbers to mean something important sometimes too -- and that is fine, in my opinion.<br />
</i><br />
It's a free world. &nbsp;You get to do what you want.<br />
But user significance for autonumbers usually leads to heartache and confusion.<br />
<br />
YMMV -- and clearly does.
</div>


<div>
<blockquote>
But user significance for autonumbers usually leads to heartache and confusion.
</blockquote>
Right. And the heartache is not the user's but the developer's. Been there, you never go there again.<br />
<br />
/gustav
</div>


<div>
all we can do is present our opinions on the use of autonumbers and let each decide for themselves. &nbsp;Like naming, there is no right or wrong convention as long as there is something consistent ~<br />
<br />
If absolute sequence is not needed, I see nothing wrong with using a number that is already unique instead of creating another one ... the engineer in me thinks it is more efficient to use what is there already ~<br />
<br />
to each their own ... we all have our own ways ~
</div>


<div>
Is there some type of ACCESS VB code out there that generates a new order number (one greater that previous)?
</div>


<div>
@crystal &amp;&nbsp;@Nick<br />
Thanks for the sample code and I appreciate it.
</div>


<div>
<div class="content wysiwyg-content">
Hi,<br />
&nbsp; I have a database system where I have ORDER table (and order item table and others). The order table has a field called &quot;Order Number&quot;.<br />
&nbsp; The data type of Order Number field is &quot;AutoNumber&quot;, so naturally it begins with 1 in a brand new table.<br />
&nbsp; However the user wants that number to start from 1,000 instead. (Apparently when you give out the order number 1, it does not leave a good impression as opposed to 1,000).<br />
<br />
&nbsp; I thought about creating 999 empty records (so that each record has a number 1 thru 999 in order number field) and delete them. That way when the user clicks [CREATE NEW ORDER] button in Order Entry Form, the system will populate 1,000 in the order number field.<br />
<br />
&nbsp;Is there a better way of accomplishing this? Any possible issues that I am not aware of?
</div>
</div>


Hi,
  I have a database system where I have ORDER table (and order item table and others). The order table has a field called "Order Number".
  The data type of Order Number field is "AutoNumber", so naturally it begins with 1 in a brand new table.
  However the user wants that number to start from 1,000 instead. (Apparently when you give out the order number 1, it does not leave a good impression as opposed to 1,000).

  I thought about creating 999 empty records (so that each record has a number 1 thru 999 in order number field) and delete them. That way when the user clicks [CREATE NEW ORDER] button in Order Entry Form, the system will populate 1,000 in the order number field.

 Is there a better way of accomplishing this? Any possible issues that I am not aware of?

Starting AutoNumber field with 1,000 in MS ACCESS

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.