DEVelopers HUT

MS Access – Auto Increment a Value

Public Function IncrementAlphaNumCode(strCode As String) As String
'Author: Graham R Seach Microsoft Access MVP Sydney, Australia
'Source: http://social.answers.microsoft.com/Forums/en-US/addbuz/thread/6cc09fc4-4a58-4e5c-aa7d-d1cc36a5483c
'Based on code developed by Stefan Hoffman MVP
Dim lngASCII As Long
Dim lngCount As Long
Dim lngLength As Long
Dim strResult As String
Dim lngValues() As Long
Const BASE_DECIMAL As Long = 10
Const BASE_HEXAVIGESIMAL As Long = 26
Const BASE As Long = 0
Const VALUE As Long = 1
 
strCode = Trim(UCase(strCode))
lngLength = Len(strCode)
 
ReDim lngValues(lngLength, 1)
lngValues(lngLength, BASE) = BASE_DECIMAL
lngValues(lngLength, VALUE) = 0
 
'Decode to plain decimal
For lngCount = 0 To lngLength - 1
    lngASCII = Asc(Mid(strCode, lngLength - lngCount, 1))
    Select Case lngASCII
        Case 48 To 57 'Numeric digit, base 10, decimal
            lngValues(lngCount, BASE) = BASE_DECIMAL
            lngValues(lngCount, VALUE) = lngASCII - 48
        Case 65 To 90 'Alphabetical character, base 26, hexavigesimal (upper case)
            lngValues(lngCount, BASE) = BASE_HEXAVIGESIMAL
            lngValues(lngCount, VALUE) = lngASCII - 65
        Case Else 'Non-alphanumeric character
            Err.Raise vbObjectError + 512, "IncrementCode", "Invalid character in source string"
    End Select
Next lngCount    'Increment

lngValues(0, VALUE) = lngValues(0, VALUE) + 1    'Calculate the carry forward
For lngCount = 0 To lngLength - 1
    If lngValues(lngCount, VALUE) >= lngValues(lngCount, BASE) Then
        lngValues(lngCount, VALUE) = 0
        lngValues(lngCount + 1, VALUE) = lngValues(lngCount + 1, VALUE) + 1
    End If
Next lngCount
 
'Encode back to mixed decimal/hexavigesimal
strResult = ""
For lngCount = 0 To lngLength
    If lngCount = lngLength And lngValues(lngCount, VALUE) = 0 Then
        Exit For
    End If
    If lngValues(lngCount, BASE) = BASE_DECIMAL Then
        strResult = Chr(lngValues(lngCount, VALUE) + 48) & strResult
    Else
        strResult = Chr(lngValues(lngCount, VALUE) + 65) & strResult
    End If
Next lngCount
 
IncrementAlphaNumCode = strResult
End Function

One Comment to “MS Access – Auto Increment a Value”

1. 
   Kim M
   June 20, 2011 at 11:22 am

   Hi — thanks so much for sharing this, it sounds like exactly what I need… but I’m not sure how to implement. Are you able to assist?

   I’ve copied the code into a new module in Access. We have a series of required elements that are part of individual case numbers where the final code will be in one of two forms: VO-2011-CC-nnnn or VO-2011-QC-nnnn. The VO is static, is the , the CC vs QC are determined by evaluating portions of the case info to assign the correct variant, and the number must be 4 digits, incrementing by 1 regardless of case type (i.e., it is a running counter, restarting at 0001 when the year changes); the counter will not start at 0001 for the current year, but will move forward from the last case number assigned upon implementation.

   Do I need to add the two alpha portions to the module code?

   Presume I need to create a formula/query that determines the CC vs QC portion which will operate similar to my Excel formula – where would that code be placed in the module?

   Can the module be inserted into the case table directly or does it need it’s own table from which I capture the result? (Perhaps by using a form to generate the number during data entry, when the determining factors have been entered?)

   The case number becomes a permanent identifier for each case. Once created, these cases are sent to external agencies throughout the state, each case number must be unique and properly identify the case.

   I can do anything in Excel, but haven’t used/developed in Access for 20 years; trying to learn VBA on the fly. (Have been pushing my regular Excel reports into Access to regain experience before creation of this particular database as it is critical and permanent.) Need to get some very large and fast growing spreadsheets into a database, they are getting unweildy (167 columns, 4 years of data, heavy on formulas and lookups). Only one staff member enters data, not likely to change, so don’t need to accommodate multiple users. Struggling a bit to automate things in Access that are a snap in Excel. :/

   Thanks for any advice you may be able to offer.
   Kim

   Reply