469,354 Members | 2,035 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,354 developers. It's quick & easy.

generate a unique serial number

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
Nov 28 '10 #1
12 7181
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
Nov 28 '10 #2
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 ;)
Nov 28 '10 #3
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?
Nov 28 '10 #4
NeoPa
32,184 Expert Mod 16PB
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"?
Nov 28 '10 #5
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function GenerateUniqueSerialNumber() As String
  5.  
  6.     On Error GoTo GenerateUniqueSerialNumber_Error
  7.  
  8.     Dim lngREC As Long
  9.     Dim strSQL As String
  10.     Dim strALPHA As String
  11.     Dim lngNUMERIC As Long
  12.     Dim daoDBS As DAO.Database
  13.     Dim daoREC As DAO.Recordset
  14.     Dim rayALPHA(1, 25) As String
  15.  
  16.     Set daoDBS = CodeDb
  17.  
  18.     strSQL = _
  19.         "SELECT " & vbCrLf & _
  20.             "[USN_ALPHA] AS [ALPHA], " & vbCrLf & _
  21.             "MAX([USN_NUMERIC]) AS [NUMERIC] " & vbCrLf & _
  22.         "FROM MyTable " & vbCrLf & _
  23.         "WHERE [USN_ALPHA] = (" & vbCrLf & _
  24.             "SELECT MAX([USN_ALPHA]) " & vbCrLf & _
  25.             "FROM MyTable) " & vbCrLf & _
  26.         "GROUP BY [USN_ALPHA];"
  27.  
  28.     Set daoREC = daoDBS.OpenRecordset(strSQL)
  29.  
  30.     If Not (daoREC.BOF And daoREC.EOF) Then
  31.         strALPHA = daoREC("ALPHA").Value
  32.         lngNUMERIC = daoREC("NUMERIC").Value
  33.     End If
  34.  
  35.     daoREC.Close
  36.     daoDBS.Close
  37.  
  38.     rayALPHA(0, 0) = "1"
  39.     rayALPHA(1, 0) = "A"
  40.     rayALPHA(0, 1) = "2"
  41.     rayALPHA(1, 1) = "B"
  42.     rayALPHA(0, 2) = "3"
  43.     rayALPHA(1, 2) = "C"
  44.     rayALPHA(0, 3) = "4"
  45.     rayALPHA(1, 3) = "D"
  46.     rayALPHA(0, 4) = "5"
  47.     rayALPHA(1, 4) = "E"
  48.     rayALPHA(0, 5) = "6"
  49.     rayALPHA(1, 5) = "F"
  50.     rayALPHA(0, 6) = "7"
  51.     rayALPHA(1, 6) = "G"
  52.     rayALPHA(0, 7) = "8"
  53.     rayALPHA(1, 7) = "H"
  54.     rayALPHA(0, 8) = "9"
  55.     rayALPHA(1, 8) = "I"
  56.     rayALPHA(0, 9) = "10"
  57.     rayALPHA(1, 9) = "J"
  58.     rayALPHA(0, 10) = "11"
  59.     rayALPHA(1, 10) = "K"
  60.     rayALPHA(0, 11) = "12"
  61.     rayALPHA(1, 11) = "L"
  62.     rayALPHA(0, 12) = "13"
  63.     rayALPHA(1, 12) = "M"
  64.     rayALPHA(0, 13) = "14"
  65.     rayALPHA(1, 13) = "N"
  66.     rayALPHA(0, 14) = "15"
  67.     rayALPHA(1, 14) = "O"
  68.     rayALPHA(0, 15) = "16"
  69.     rayALPHA(1, 15) = "P"
  70.     rayALPHA(0, 16) = "17"
  71.     rayALPHA(1, 16) = "Q"
  72.     rayALPHA(0, 17) = "18"
  73.     rayALPHA(1, 17) = "R"
  74.     rayALPHA(0, 18) = "19"
  75.     rayALPHA(1, 18) = "S"
  76.     rayALPHA(0, 19) = "20"
  77.     rayALPHA(1, 19) = "T"
  78.     rayALPHA(0, 20) = "21"
  79.     rayALPHA(1, 20) = "U"
  80.     rayALPHA(0, 21) = "22"
  81.     rayALPHA(1, 21) = "V"
  82.     rayALPHA(0, 22) = "23"
  83.     rayALPHA(1, 22) = "W"
  84.     rayALPHA(0, 23) = "24"
  85.     rayALPHA(1, 23) = "X"
  86.     rayALPHA(0, 24) = "25"
  87.     rayALPHA(1, 24) = "Y"
  88.     rayALPHA(0, 25) = "26"
  89.     rayALPHA(1, 25) = "Z"
  90.  
  91.     If lngNUMERIC = 999999 Then
  92.         lngNUMERIC = 111111
  93.         If Right(strALPHA, 1) = "Z" Then
  94.             If Left(strALPHA, 1) = "Z" Then
  95.                 MsgBox _
  96.                 "Error: You have already used up all " & vbCrLf & _
  97.                 "possible alpha numeric combinations.", vbCritical, "All Used Up!"
  98.             Else
  99.                 For lngREC = 0 To 25
  100.                     If Left(strALPHA, 1) = rayALPHA(1, lngREC) Then
  101.                         strALPHA = rayALPHA(1, lngREC + 1) & "A"
  102.                         Exit For
  103.                     End If
  104.                 Next
  105.             End If
  106.         Else
  107.             For lngREC = 0 To 25
  108.                 If Right(strALPHA, 1) = rayALPHA(1, lngREC) Then
  109.                     strALPHA = Left(strALPHA, 1) & rayALPHA(1, lngREC + 1)
  110.                     Exit For
  111.                 End If
  112.             Next
  113.         End If
  114.     Else
  115.         lngNUMERIC = lngNUMERIC + 1
  116.     End If
  117.  
  118.     GenerateUniqueSerialNumber = strALPHA & CStr(lngNUMERIC)
  119.  
  120. GenerateUniqueSerialNumber_Exit:
  121.     strSQL = ""
  122.     strALPHA = ""
  123.     lngREC = Empty
  124.     lngNUMERIC = Empty
  125.     Set daoREC = Nothing
  126.     Set daoDBS = Nothing
  127.     Exit Function
  128.  
  129. GenerateUniqueSerialNumber_Error:
  130.     MsgBox "Error " & Err.Number & vbCrLf & Err.Description, "ERROR:"
  131.     Err.Clear
  132.     Resume GenerateUniqueSerialNumber_Exit
  133.  
  134. End Function
  135.  
Nov 29 '10 #6
ADezii
8,800 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Public Function fGenerateUniqueSerialNum()
  2. Dim intPosCtr As Byte
  3. Dim strBuild As String
  4. Randomize
  5.  
  6. For intPosCtr = 1 To 8      '2 Alphas/6 Numeric
  7.   If intPosCtr < 3 Then     'Alpha positions 1 and 2
  8.     strBuild = strBuild & Chr$(Int((26) * Rnd + 65))    'A to Z
  9.   Else                      'Numeric positions 3 to 8
  10.     strBuild = strBuild & CStr(Int(Rnd * 10))
  11.   End If
  12. Next
  13.  
  14. fGenerateUniqueSerialNum = strBuild
  15. End Function
Sample OUTPUT (25 Runs):
Expand|Select|Wrap|Line Numbers
  1. FK089124
  2. ZG410697
  3. VP074019
  4. YK787230
  5. TC983631
  6. GP914590
  7. AJ778185
  8. UF109668
  9. JF468179
  10. TM053270
  11. WH766492
  12. VX607518
  13. YR490893
  14. VZ319172
  15. FB768009
  16. BM253267
  17. II180499
  18. RU807448
  19. PS459616
  20. KI714160
  21. CW231591
  22. DA398771
  23. ND671751
  24. LB223929
  25. DC336604
Nov 29 '10 #7
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"......
Dec 4 '10 #8
NeoPa
32,184 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Function GetNext() As String
  2.     Dim strMax As String
  3.     Dim lngNum As Long
  4.  
  5.     strMax = Nz(DMax("[SerNoField]", "[YourTable]"), "AA000000")
  6.     lngNum = CLng(Mid(strMax, 3))
  7.     If lngNum > 999998 Then lngNum = 0
  8.     GetNext = Left(strNext, 2) & Format(lngNum + 1, "000000")
  9. End Function
Dec 4 '10 #9
NeoPa
32,184 Expert Mod 16PB
I left an important bit out of the last post. It should have read :
Expand|Select|Wrap|Line Numbers
  1. Private Function GetNext() As String
  2.     Dim strMax As String
  3.     Dim lngNum As Long
  4.     Dim intOne As Integer, intTwo As Integer
  5.  
  6.     strMax = Nz(DMax("[SerNoField]", "[YourTable]"), "AA000000")
  7.     lngNum = CLng(Mid(strMax, 3))
  8.     intOne = Asc(strMax)
  9.     intTwo = Asc(Mid(strMax, 2))
  10.     If lngNum > 999998 Then
  11.         lngNum = 0
  12.         If intTwo > Asc("Y") Then
  13.             intTwo = Asc("A") - 1
  14.             intOne = intOne + 1
  15.         End If
  16.         intTwo = intTwo + 1
  17.     End If
  18.     lngNum = lngNum + 1
  19.     GetNext = Chr(intOne) & Chr(intTwo) & Format(lngNum, "000000")
  20. End Function
Dec 4 '10 #10
colintis
255 100+
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?
Dec 5 '10 #11
All numbers must be consistent.
Dec 5 '10 #12
NeoPa
32,184 Expert Mod 16PB
Ivan, have you looked at post #10? You haven't responded to it.
Dec 5 '10 #13

Post your reply

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

Similar topics

15 posts views Thread by tom | last post: by
5 posts views Thread by | last post: by
1 post views Thread by Paul | last post: by
5 posts views Thread by Jassim Rahma | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.