Microsoft Access - need to add a counter for each record

I have a requirement where I have to add a document number for each record. The sequential number is unique for each year, and is 3 chars long. So if we are on number 187 now, the next record is to be 2015/188 and so on. Then the first record for next year will be 2016/001. And so on...

What is the best way to do this please?
Rick DangerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
To keep this simple, you could simply use code like this
Make a field called DocNum in the table, set the datatype as Long Integer
Make the first record a value of 1.
Then use code like this to increment the values and add the slash and the year

Public Function GetNextDocNumber () as string
Dim lngMaxDocNum as long
lngMaxDocNum =Dmax("DocNum", "YourTable")
    GetDocNumber =lngMaxDocNum +1 & "/" & Year(Date())
End Function
Jeffrey CoachmanMIS LiasonCommented:
Ignore that code,

here is a simple,sample DB:
Jeffrey CoachmanMIS LiasonCommented:
New code for the Full DocNum (The original code in the sample had the year and the number reversed)

Public Function GetNextDocNumber() As String
    GetNextDocNumber = Year(Date) & "/" & GetDocNumber
End Function

Sorry about that...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Rick DangerAuthor Commented:
Thanks - nice easy answer to follow
Dale FyeOwner, Developing Solutions LLCCommented:
I prefer to store this value in a separate table (tbl_NextValue) and use a function to update that value.  This table can contain values for multiple tables/IDs and might  would look something like:

ValueName(text)      ValueYear(integer)  NextValue(long int)
DocNumber              2015                          188
InvNumber                2015                          0

Then you would use a function like the following to get the new value.  This assumes that you actually want the year appended to the beginning of the next value:
Public fnNextValue(ValName as string, Optional ValYear as Variant = NULL) as string

    Dim rs as DAO.Recordset
    Dim strSQL as string

    if isnull(valYear) then valYear = Year(Date())
    strSQL = "SELECT * FROM tbl_NextValue " _
                 & "WHERE [ValueName] = '" & ValName & "' " _
                 & "AND [ValueYear] = " & clng(ValYear)
    set rs = currentdb.OpenRecordset(strsql, , dbfailonerror)
    if rs.eof then
        !ValueName = ValName
        !ValueYear = valYear
        !NextValue = 2
        fnNextValue = 1
        fnNextValue = !NextValue
        !NextValue = !NextValue + 1
    End IF

    on error resume next
    set rs = nothing
    Exit Function

    debug.print "fnNextValue", err.number, err.description
    msgbox err.number & vbcrlf & err.description, , "fnNextValue error"
    Resume ProcExit

Exit Function

Open in new window

This actually stores the next value to be retrieved in the table and will insert a new record in the table when the year changes or when you provide a new year in the function.
Jeffrey CoachmanMIS LiasonCommented:
Ultimately, this data should be stored in separate fields
The incremented number should be an AutoNumber field, ...and the year should just be pulled form the current year
Then you can concatenate the two for display purposed.

As he field is now, it is text, ...and there fore hard to query for a specific DocNumnber based on the year.
(or a range of DocNumber/years)

Also investigate Dales Post, there are many ways to do this...
Rick DangerAuthor Commented:
Thanks to you both - I actually like Dale's answer, but I've already given out the points!
Rey Obrero (Capricorn1)Commented:
what will you do if a record somewhere at the middle was deleted?
do you still want to see a continuous counter or it is okay to have a gap?
Rick DangerAuthor Commented:
The gap will have to remain.
Dale FyeOwner, Developing Solutions LLCCommented:
no problem WRT points.  good luck with your project.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.