You are here:

Using MS Access/Access auto number reset every year


QUESTION: Next you will use this line of VBA code to populate that control:

Me.txtIncrement = Nz(DMax("[Increment]","tablename","Year([datefield]) = " & Year(Me.[datecontrol]),0)+1

where tablename is the name of your table, datefield, the name of your date field and datecontrol, the name of the date control on the form.  

Can you tell me if by date control you mean a separate field called date control type date/time

ANSWER: No, I mean a control on your form that is bound to the date field in your table.

Hope this helps,

---------- FOLLOW-UP ----------


Thank your for your reply, I am just not getting this. let me recap my scenario as I have it maybe you can shed some light.

I want to have as a document number the year-sequential numbering of documents entered that year (i.e., 13-001 --> 13-xxx) in the next year i need it to change to the new year (i.e., 14-001 --> 14-xxx);sequentially.

I have one table: tblTest
id = type autonumber
increment = type long integer
dateField = type date/time
txtdata type text

datecontrol=name of control bound to datefield

Me.Increment = Nz(DMax("[Increment]", "tblJNI", "Year([dateField])= " & Year(Me.[datecontrol])), 0) + 1
txtdata = Format(dateField, "yy") & "-" & Increment

What I get is the increments in the way i need them but it increments when I scroll backwards through the records as well. Any thoughts as to what i am doing wrong here?

ANSWER: Where is that code? I suspect its in the wrong place which is the source of your problem.


---------- FOLLOW-UP ----------

QUESTION: the code is on Private sub form current()

where should it be?

That's what I thought. That is the worst place for the code. The On Current event fires whenever you change records. So everytime you move from record to record it will change the increment.

My blog says that you need to use some event just before you save the record. You also need to commit the record immediately after you do the increment.

What event you choose depends on your workflow. You might have a Save button on your form or a generate ID button. But definitely not On current.

Also, you should NOT be storing this code. You should NOT have a txtData field in your table. You should have a control on your form with the expression:
Format(dateField, "yy") & "-" & Increment

You use that expression wherever you want to display the code. This is all detailed in my blog on Sequential Numbering (

Hope this helps,

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.