Link to home
Start Free TrialLog in
Avatar of Rick Danger
Rick DangerFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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?
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

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
Ignore that code,

here is a simple,sample DB:
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rick Danger


Thanks - nice easy answer to follow
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.
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...
Thanks to you both - I actually like Dale's answer, but I've already given out the points!
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?
The gap will have to remain.
no problem WRT points.  good luck with your project.