I need to create a computer generated case number

[nfontaine]

I am creating a database in Access 2002. I need to create a computer generated case number that starts with the year and is in the form YY-nnnnnn (6 digit number) that increments by 1. I am not a programmer but have some experience creating small databases. I am hoping that someone can assist me. Thank you

[ADezii]

The easiest Method, in my mind, is to:

1. Manually enter ('seed), the 1st Case Number in your Table, say Table1, into the Field [CaseNum].
2. Execute the following Function which will Increment the Number, then properly Format it.
   

   Public Function fIncrementCaseNumber() As String
   Dim bytYear As Byte
   Dim strLastCaseNum As String
   Dim bytCurrentYear As Byte
    
   strLastCaseNum = DMax("[CaseNum]", "Table1")
    
   bytYear = CByte(Left$(strLastCaseNum, 2))   'Extract the Year
   bytCurrentYear = Format$(Date, "yy")        'Current Year
    
   If bytYear <> bytCurrentYear Then           'A Year change?
     fIncrementCaseNumber = CStr(bytCurrentYear) & "-000001"
   Else
     'Increment and Format the Numeric Case Number Component
     fIncrementCaseNumber = Left$(strLastCaseNum, 3) & Format$(Val(Right$(strLastCaseNum, 6) + 1), "000000")
   End If
   End Function

   First entry is 10-000001

   'Execute Function:
   fIncrementCaseNumber()

   'OUTPUT:
   10-000002

[NeoPa]

This question, in one form or another, is asked quite regularly on here.

There are a number of issues to consider :

1. Determining the code itself.
   
   1. Check the table as it stands.
   2. Find the largest value that matches the current year.
   3. If none exists yet, assume this to be zero (0).
   4. Increment this number.
   5. Format the code using the year value and this newly determined number.
   
   
2. Where (when) to determine the code.
   
   This should be done in the Form_BeforeInsert() event procedure. It can only make sense if it is applied immediately it is determined.
   
   
3. When to display the code (related to 2 above).
   
   Not when entering the data for the record. As this cannot be known until the point the data is actually saved away (See point #2 above) then it would be invalid to display anything here until it has been. This is synonymous with an AutoNumber field, which is not shown until the record has been created.

Having discussed the concepts we can look at the code. I've tried to make it as clear as possible what each step is doing, but you'll have to put your own control, field and table names in yourself :

Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim strWhere As String
 
    strWhere = "[CodeField] Like '" & Format(Date, "yy") & "-*'"
    Me.CodeControl = Format(Date, "yy") & "-" & _
                     Format(Nz(DMax("Val(Right([CodeField],6))", _
                                    "[Table]", _
                                    strWhere), 0) + 1, "000000")
End Sub

Welcome to Bytes!