You are here:

Using MS Access/Resetting the Autonumber in ms access


Hi there,
First of all I would like to thank you in advance for any help you can provide to me on this topic.
I am creating a database that will track purchase orders.  An example of our numbers that are used for tracking would be:  13-1005.  The 13 is the year and the 0015 is the autonumber (0015 is the 15th purchase for 2013).  On April 1, 2014 our number will change to 14-0001(0001 the first purchase for 2014), then the second purchase order will be 14-0002 and so on. How can I reset the autonumber to change back to 0001 once April 1, 2014 hits, then the next year when April 1, 2015 hits, I need it to change again so that the number will be 15-0001 and so on. Thanks so much for all of your help.  :)

You can't do this with an autonumber datatype. The SOLE purpose of an autonumber is to provide a unique identifier for a record. Therefore its unsuitable for a numbering system like you describe.

Please see my blog article on Sequential Numbering. I discuss a scenario very similar to yours in that article. You will, however, have to be a little creative with the Year issue since you are using a fiscal year rather than a calendar year. If you need help with that let me know.

A reminder also that you don't store this Order number. You store (or calculate) the Fiscal year and the sequence number and then concatenate them together for display. This means you should still use an autonumber as your primary key internally to the database.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.