Creating an Autonumber with a Prefix for different categories while including the Year and Month

I've found possible solutions for problems similar to mine, but not enough to get me where I need to go. What I am looking for is an Incrementing Autonumber including the Month, Year, and a Letter prefix at the beginning of the Autonumber based on the users choice.

For example: A06-05-001, A06-05-002....A06-05-018, A06-05-019
          B06-05-001, B06-05-002....B06-05-025, B06-05-026
          C06-05-001, C06-05-002....C06-05-056, C06-05-057

"A" = Apples (Category), "B" Bananas (Category), "C" Cherries (Category)
"06" = Current year
"05" = Current month
"001" = Counter incrementing for each category

I want to be able to have the user choose a category from a drop down list in a form so it can apply a prefix to the Autonumber including the year and month. I will need to have each category have its own increment so that if a user chooses category "C", the counter will automatically check the last number used for Category "C" and not the others, and apply the next sequential number. The tricky part for this is I am trying to have the counters reset every month for all categories.

I don’t mind using VBA if it will accomplish this task. Hope this makes sense. Thanks in advance for your help.

Who is Participating?
If u had it as one field - format like u said    B06-05-001
field in table was called ProdKey

say you had two fields on your form

Category (A,B,C etc)

DateFld (if not present, we could just use Now() - this returns current date, but lets assume a date field is present)

'Lets create a function to get the next counter and one to build one

public function GetNextCounter(byval sCategory as String, byval sDate as string) as long

    Dim sWhere as string

    GetNextCounter = -1         'indicates failure

    sField = "Left(ProdKey,1) = '" & sCategory & "' AND Mid(ProdKey,2,5) = '" & format(year(sDate),"00") & "-" & format(Month(sDate),"00") & "'"

    GetNextCounter = DMAX("Right(ProdKey,3)","tblProdKeys",sWhere)

    If GetNextCounter > 0 then GetNextCounter = GetNextCounter + 1 else GetNextCounter = -1
end function

public function BuildCounter(byval sCategory as String, byval sDate as String, optional byval lCounter as integer = -1) as String

    'If counter is -1 then we have to do the hard work

    dim lNextCounter as integer

    if lCounter = -1 then lNextCounter = GetNextCounter(sCategory, sDate) else lNextCounter = lCounter

    BuildCounter = sCategory & format(year(sDate),"00") & "-" & format(month(sDate),"00") & "-" & format(lNextCounter,"000")
end function

Suggest you use 3 fields


or make use of one field with delimiters Category-CurrDate-Counter

say u had the 3 field part, to check the max id, u can simply do a DMAX

DMAX("Counter","mytable","Category = '" & somecat & "' AND CurrDate = " & format(somedate,"YYMM"))

basically when u store records, u store category as string field "A","B" etc
and date field as date entered by user but formatted

so year is first

Now when u run max, it gives u the highest counter value. You can then increment to get the next value

If u use the one field approach, u would need to make use of string manipulation functins like LEFT and MID
masterimiAuthor Commented:
Thank you for the quick responses rockiroads. I will try your suggestions and get back ASAP. I haven't used VBA in a while so I'll get on it quickly and see if it works. Will these counters reset each month also? Thanks.
Counters dont reset, but in theory they should do

if we check for specific month and year, and it doesnt find anything then counter should become 1
masterimiAuthor Commented:
I tried to duplicate the suggestions you made rockiroads but I ran into a few problems on my part. I created a small DB based on what I have gathered so far including your comments and other resources. I am getting close but still running into some problems.

Table named "Codes":
Field: Code_Desc
Field: Last_Nbr_Assigned

Table named "Events":
Field: Seq_Number
Field: Item_Type
Field: Event_Description

   -Sequential # Text Box
   -Item Type Combo Box
   -Description Text Box

The item type combo box has the list of items such as Apples, Bananas, and Cherries. When the user chooses Apples, the locked Sequential #Text Box automatically generates the correct number such as A06-07-001 (on Event: Lost Focus). The problem I have now is the counter is not going up. This is what I have so far in VB.


Private Sub Item_Type_LostFocus()

    'Assign sequential number
    If IsNull(Seq_Number) = True Then
        'An Item_Type must be selected
        If IsNull(Item_Type.Value) = False Then
            Seq_Number = NewSeqNumber(Item_Type.Value)
        End If
    End If
End Sub


Option Compare Database

Function NewSeqNumber(pItem_Type) As String

    Dim db             As Database
    Dim LSQL           As String
    Dim LUpdate        As String
    Dim LInsert        As String
    Dim Lrs            As DAO.Recordset
    Dim LSeqNumber     As String
    Dim LYear          As String
    Dim LMonth         As String
    On Error GoTo Err_Execute
    Set db = CurrentDb()
    'Retrieve last 2 digits of current year
    LYear = Mid(CStr(Year(Date)), 3, 2)
    'Retrieve 2 digits of current month
    LMonth = Format(Date, "mm")
    'Retrieve last number assigned for item_type/year/month combination
    LSQL = "Select Last_Nbr_Assigned from Codes"
    LSQL = LSQL & " where Code_Desc = '" & pItem_Type & LYear & LMonth & "'"
    Set Lrs = db.OpenRecordset(LSQL)
    'If no records were found, create a new item_type/year/month combination in
    'the Codes table and set initial value to 1
    If Lrs.EOF = True Then
        LInsert = "Insert into Codes (Code_Desc, Last_Nbr_Assigned)"
        LInsert = LInsert & " values "
        LInsert = LInsert & "('" & pItem_Type & LYear & "-" & LMonth & "', 1)"
        db.Execute LInsert, dbFailOnError
        'New sequential number is formatted as "C05-07-001", for example
        LSeqNumber = pItem_Type & LYear & "-" & LMonth & "-" & Format(1, "000")
        'Determine new sequential number
        'New sequential number is formatted as "C05-07-001", for example
        LSeqNumber = pItem_Type & LYear & "-" & LMonth & "-" & Format(Lrs("Last_Nbr_Assigned") + 1, "000")
        'Increment counter in Codes table by 1
        LUpdate = "Update Codes"
        LUpdate = LUpdate & " set Last_Nbr_Assigned = " & Format(Lrs("Last_Nbr_Assigned") + 1, "000")
        LUpdate = LUpdate & " where Code_Desc = '" & pItem_Type & LYear & "-" & LMonth & "-" & "'"
        db.Execute LUpdate, dbFailOnError
    End If
    Set Lrs = Nothing
    Set db = Nothing
    NewSeqNumber = LSeqNumber
    Exit Function
    'An error occurred, return blank string
    NewSeqNumber = ""
    MsgBox "An error occurred while trying to determine the next sequential number to assign."
End Function

It doesn’t seem to be tracking the counter in the Codes table in field Code_Desc. It only shows A06-07 and not the three digit counter. So when I go to created the next sequential number, I get a Run-time error saying “Field ‘Events.Seq_Number’ cannot be a zero length string. Thanks all.
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.

All Courses

From novice to tech pro — start learning today.