asked on

Incremental Numbering for clients & Year

I need to create an incremental numbering system based on a client ID and the year. Right now I have a numbering system that works well and increments based on the client that is logged in. It numbers the client records sequentially all starting from 0001. But when the year changes I need that numbering system to start back at 0001. Here's what I currently have that is working fine but now I need to somehow change it to start over when the new year starts

Dim i, a
 i = Me.cboClinicName
Me.txtCRecordID = [txtClinicCode] & Format(Date(),"yy") & Nz(DMax("CRecordID", "tblCogginsDetail", "ClinicName = " & i), 0) + 1

Open in new window

So here's how it looks now for each record

When the clinic name is NEHL or KOS for example:

NEHL130001
NEHL130002
KOS130001
KOS130002
KOS130003

So now what I need to do is restart the last 4 digits of the above sequence when the year changes. For example:

NEHL130001
NEHL130002
NEHL140001

etc...

How can I rewrite my code to restart this when the year changes?

ASKER CERTIFIED SOLUTION