generate a unique serial number

[Ivan Popov]

I need to generate a unique serial number for each field in the table. The serial number shall consist of two letters and six numbers, for example AA123456. Someone can suggest how to do it in MS Access

[Stephen Parker]

Can you provide any more information? You say each field in the table is that correct or did you mean each row / record? Is there a specific reason you have to have it in the described format of "two letters and 6 numbers"? Just curious as that sounds very strange. I see you have a lot of views with zero replies. It may be better if there was more information to work with to better understand what you are trying to accomplish. Take Care & God Bless ~ Stephen

[Ivan Popov]

Thanks Stephen! There is table with information about cars and its owners and some other documents, all this information will be writen on the special list (one row for one car and its owner), I need a unique serial number for each row in the table. Two letters and 6 numbers is a standart for this documents. Valid is a separate generator for letters and numbers, in diferent fields. I hope someone will help me ;)

[Stephen Parker]

One more question: When you say "Valid is a seperate generator for letters and numbers, in different fields" are you saying that the value has to be in two seperate fields or that it can be in two seperate fields?

[NeoPa]

Ivan, You need to explain your question more clearly (as Stephen says). At the moment, we have no information to use to determine which two letters should be used for any particular record. Without this it will be impossible to provide a workable solution.

More questions for you.

1. You say the value must be unique (and I'll assume you are referring to unique per record rather than per field). Does the number part need to be unique?
2. Does the number part need to be a value one more than the last value used, that matches the same two letters? EG. If you needed a value for a "CD" record and the last one used was "CD220431", would the next one need to be "CD220432"?

[Stephen Parker]

With the limited information provided the following function should be easily modified to achieve the desired results regardless of wether or not you keep the values in two seperate fields or one single field:

Option Compare Database
Option Explicit

Public Function GenerateUniqueSerialNumber() As String

    On Error GoTo GenerateUniqueSerialNumber_Error
    
    Dim lngREC As Long
    Dim strSQL As String
    Dim strALPHA As String
    Dim lngNUMERIC As Long
    Dim daoDBS As DAO.Database
    Dim daoREC As DAO.Recordset
    Dim rayALPHA(1, 25) As String
    
    Set daoDBS = CodeDb
    
    strSQL = _
        "SELECT " & vbCrLf & _
            "[USN_ALPHA] AS [ALPHA], " & vbCrLf & _
            "MAX([USN_NUMERIC]) AS [NUMERIC] " & vbCrLf & _
        "FROM MyTable " & vbCrLf & _
        "WHERE [USN_ALPHA] = (" & vbCrLf & _
            "SELECT MAX([USN_ALPHA]) " & vbCrLf & _
            "FROM MyTable) " & vbCrLf & _
        "GROUP BY [USN_ALPHA];"

    Set daoREC = daoDBS.OpenRecordset(strSQL)
    
    If Not (daoREC.BOF And daoREC.EOF) Then
        strALPHA = daoREC("ALPHA").Value
        lngNUMERIC = daoREC("NUMERIC").Value
    End If
    
    daoREC.Close
    daoDBS.Close

    rayALPHA(0, 0) = "1"
    rayALPHA(1, 0) = "A"
    rayALPHA(0, 1) = "2"
    rayALPHA(1, 1) = "B"
    rayALPHA(0, 2) = "3"
    rayALPHA(1, 2) = "C"
    rayALPHA(0, 3) = "4"
    rayALPHA(1, 3) = "D"
    rayALPHA(0, 4) = "5"
    rayALPHA(1, 4) = "E"
    rayALPHA(0, 5) = "6"
    rayALPHA(1, 5) = "F"
    rayALPHA(0, 6) = "7"
    rayALPHA(1, 6) = "G"
    rayALPHA(0, 7) = "8"
    rayALPHA(1, 7) = "H"
    rayALPHA(0, 8) = "9"
    rayALPHA(1, 8) = "I"
    rayALPHA(0, 9) = "10"
    rayALPHA(1, 9) = "J"
    rayALPHA(0, 10) = "11"
    rayALPHA(1, 10) = "K"
    rayALPHA(0, 11) = "12"
    rayALPHA(1, 11) = "L"
    rayALPHA(0, 12) = "13"
    rayALPHA(1, 12) = "M"
    rayALPHA(0, 13) = "14"
    rayALPHA(1, 13) = "N"
    rayALPHA(0, 14) = "15"
    rayALPHA(1, 14) = "O"
    rayALPHA(0, 15) = "16"
    rayALPHA(1, 15) = "P"
    rayALPHA(0, 16) = "17"
    rayALPHA(1, 16) = "Q"
    rayALPHA(0, 17) = "18"
    rayALPHA(1, 17) = "R"
    rayALPHA(0, 18) = "19"
    rayALPHA(1, 18) = "S"
    rayALPHA(0, 19) = "20"
    rayALPHA(1, 19) = "T"
    rayALPHA(0, 20) = "21"
    rayALPHA(1, 20) = "U"
    rayALPHA(0, 21) = "22"
    rayALPHA(1, 21) = "V"
    rayALPHA(0, 22) = "23"
    rayALPHA(1, 22) = "W"
    rayALPHA(0, 23) = "24"
    rayALPHA(1, 23) = "X"
    rayALPHA(0, 24) = "25"
    rayALPHA(1, 24) = "Y"
    rayALPHA(0, 25) = "26"
    rayALPHA(1, 25) = "Z"
        
    If lngNUMERIC = 999999 Then
        lngNUMERIC = 111111
        If Right(strALPHA, 1) = "Z" Then
            If Left(strALPHA, 1) = "Z" Then
                MsgBox _
                "Error: You have already used up all " & vbCrLf & _
                "possible alpha numeric combinations.", vbCritical, "All Used Up!"
            Else
                For lngREC = 0 To 25
                    If Left(strALPHA, 1) = rayALPHA(1, lngREC) Then
                        strALPHA = rayALPHA(1, lngREC + 1) & "A"
                        Exit For
                    End If
                Next
            End If
        Else
            For lngREC = 0 To 25
                If Right(strALPHA, 1) = rayALPHA(1, lngREC) Then
                    strALPHA = Left(strALPHA, 1) & rayALPHA(1, lngREC + 1)
                    Exit For
                End If
            Next
        End If
    Else
        lngNUMERIC = lngNUMERIC + 1
    End If

    GenerateUniqueSerialNumber = strALPHA & CStr(lngNUMERIC)

GenerateUniqueSerialNumber_Exit:
    strSQL = ""
    strALPHA = ""
    lngREC = Empty
    lngNUMERIC = Empty
    Set daoREC = Nothing
    Set daoDBS = Nothing
    Exit Function

GenerateUniqueSerialNumber_Error:
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description, "ERROR:"
    Err.Clear
    Resume GenerateUniqueSerialNumber_Exit

End Function

[ADezii]

I'm still slightly confused, but if you are looking for a Unique Serial Number in the Format of (AA123456) for each Record in a Table, then the following Function will do the trick. I'll post the Code along with 25 Sample Runs. There will be 676,000,000 possible combinations (26*26*10*10*10 *10*10*10), so the chance of duplication is extremely small to say the least. If you also wish to check for Duplication within the Function, this will require a simple adjustment.

Public Function fGenerateUniqueSerialNum()
Dim intPosCtr As Byte
Dim strBuild As String
Randomize

For intPosCtr = 1 To 8      '2 Alphas/6 Numeric
  If intPosCtr < 3 Then     'Alpha positions 1 and 2
    strBuild = strBuild & Chr$(Int((26) * Rnd + 65))    'A to Z
  Else                      'Numeric positions 3 to 8
    strBuild = strBuild & CStr(Int(Rnd * 10))
  End If
Next

fGenerateUniqueSerialNum = strBuild
End Function

Sample OUTPUT (25 Runs):

FK089124
ZG410697
VP074019
YK787230
TC983631
GP914590
AJ778185
UF109668
JF468179
TM053270
WH766492
VX607518
YR490893
VZ319172
FB768009
BM253267
II180499
RU807448
PS459616
KI714160
CW231591
DA398771
ND671751
LB223929
DC336604

[Ivan Popov]

To NeoPa:
Serial number should be unique per record.
The number part need to be a value one more than the last value used.
For example, the first one "AA000001", second "AA000002" .... then "AA999999". .... then "AB000001"..... .

[NeoPa]

Ivan Popov:
For example, the first one "AA000001", second "AA000002" .... then "AA999999". .... then "AB000001"..... .

This example certainly clarifies what you are after.

You would then need code somewhat similar to this :

Private Function GetNext() As String
    Dim strMax As String
    Dim lngNum As Long

    strMax = Nz(DMax("[SerNoField]", "[YourTable]"), "AA000000")
    lngNum = CLng(Mid(strMax, 3))
    If lngNum > 999998 Then lngNum = 0
    GetNext = Left(strNext, 2) & Format(lngNum + 1, "000000")
End Function

[NeoPa]

I left an important bit out of the last post. It should have read :

Private Function GetNext() As String
    Dim strMax As String
    Dim lngNum As Long
    Dim intOne As Integer, intTwo As Integer

    strMax = Nz(DMax("[SerNoField]", "[YourTable]"), "AA000000")
    lngNum = CLng(Mid(strMax, 3))
    intOne = Asc(strMax)
    intTwo = Asc(Mid(strMax, 2))
    If lngNum > 999998 Then
        lngNum = 0
        If intTwo > Asc("Y") Then
            intTwo = Asc("A") - 1
            intOne = intOne + 1
        End If
        intTwo = intTwo + 1
    End If
    lngNum = lngNum + 1
    GetNext = Chr(intOne) & Chr(intTwo) & Format(lngNum, "000000")
End Function

[colintis]

Just one question about the first 2 letters on your serial number. Do they increment as the serial reaching 999999? looks like a very long time to get to AB to me as a car dealer. Or is there actually some criterias that these letters will increment instead of waiting the 999999 of AA?

[Ivan Popov]

All numbers must be consistent.

[NeoPa]

Ivan, have you looked at post #10? You haven't responded to it.