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
So here's how it looks now for each record
When the clinic name is NEHL or KOS for example:
So now what I need to do is restart the last 4 digits of the above sequence when the year changes. For example:
How can I rewrite my code to restart this when the year changes?