By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,361 Members | 1,700 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,361 IT Pros & Developers. It's quick & easy.

I need to create a computer generated case number

P: 1
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
Sep 8 '10 #1
Share this Question
Share on Google+
2 Replies


ADezii
Expert 5K+
P: 8,669
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.
    Expand|Select|Wrap|Line Numbers
    1. Public Function fIncrementCaseNumber() As String
    2. Dim bytYear As Byte
    3. Dim strLastCaseNum As String
    4. Dim bytCurrentYear As Byte
    5.  
    6. strLastCaseNum = DMax("[CaseNum]", "Table1")
    7.  
    8. bytYear = CByte(Left$(strLastCaseNum, 2))   'Extract the Year
    9. bytCurrentYear = Format$(Date, "yy")        'Current Year
    10.  
    11. If bytYear <> bytCurrentYear Then           'A Year change?
    12.   fIncrementCaseNumber = CStr(bytCurrentYear) & "-000001"
    13. Else
    14.   'Increment and Format the Numeric Case Number Component
    15.   fIncrementCaseNumber = Left$(strLastCaseNum, 3) & Format$(Val(Right$(strLastCaseNum, 6) + 1), "000000")
    16. End If
    17. End Function
    Expand|Select|Wrap|Line Numbers
    1. First entry is 10-000001
    Expand|Select|Wrap|Line Numbers
    1. 'Execute Function:
    2. fIncrementCaseNumber()
    Expand|Select|Wrap|Line Numbers
    1. 'OUTPUT:
    2. 10-000002
Sep 8 '10 #2

NeoPa
Expert Mod 15k+
P: 31,661
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeInsert(Cancel As Integer)
  2.     Dim strWhere As String
  3.  
  4.     strWhere = "[CodeField] Like '" & Format(Date, "yy") & "-*'"
  5.     Me.CodeControl = Format(Date, "yy") & "-" & _
  6.                      Format(Nz(DMax("Val(Right([CodeField],6))", _
  7.                                     "[Table]", _
  8.                                     strWhere), 0) + 1, "000000")
  9. End Sub
Welcome to Bytes!
Sep 9 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.