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?
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
0
Jeffrey CoachmanMIS LiasonCommented:
Ignore that code,

here is a simple,sample DB:
Database86.mdb
0
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...
;-)
Jeff
0

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

rick_dangerAuthor Commented:
Thanks - nice easy answer to follow
0
Dale FyeCommented:
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
        .AddNew
        !ValueName = ValName
        !ValueYear = valYear
        !NextValue = 2
        .Update
        fnNextValue = 1
    Else
        fnNextValue = !NextValue
        .Edit
        !NextValue = !NextValue + 1
        .Update
    End IF

ProcExit:
    on error resume next
    rs.close
    set rs = nothing
    Exit Function

ProcError:
    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.
0
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, ...as there are many ways to do this...
Jeff
0
rick_dangerAuthor Commented:
Thanks to you both - I actually like Dale's answer, but I've already given out the points!
0
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?
0
rick_dangerAuthor Commented:
The gap will have to remain.
0
Dale FyeCommented:
no problem WRT points.  good luck with your project.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.