Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2006-07-23
Medium Priority
Last Modified: 2008-02-01
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.

Question by:masterimi
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 65

Expert Comment

ID: 17164302
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
LVL 65

Accepted Solution

rockiroads earned 2000 total points
ID: 17164378
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


Author Comment

ID: 17164399
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.
LVL 65

Expert Comment

ID: 17165612
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

Author Comment

ID: 17172146
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.

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question