Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Pommetje77 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Location
    the Netherlands
    Posts
    37

    Generating random and unique 3 digit code and 3 letter code

    Hello all,
    I am stuck!
    I searched all the forums, but cannot find specifically what I need.

    I am looking for a code to randomly generate a unique 3 digit numerical code.
    I understood this shall be done by Do, while and loop, however I am a Noob to VBA.

    I need this in my manufacturing database to give suppliers a 3 digit unique number identifier.
    I am making use of MS Access 2013 version under windows 7 professional.

    So what I am actually like is a button 'cmdGenerate' to Generate a unique random number between 100 and 999.


    If the number has been generated, store it in a textbox 'txtSupplierCode'
    This textbox is bound to table 'Suppliers'.

    The code shall even check the table on generated codes, so manually assigned codes from the past shall not be generated again.

    Additionally, I would like the same for 2 letter code. (I understand that this is a different question, however it shall not look very different while only the definition of numbers into letters is different.

    Hope one of you can help me
    Thanks in advance

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Why a random number? Why not an autonumber --controlled by Access and guaranteed to be unique.

  3. #3
    Pommetje77 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Location
    the Netherlands
    Posts
    37
    I expected this question, I want to control the number myself by a script, so I can adapt it in the future.
    Beside of this, We currently have a supplier excel list with already lots of numbers randomly added by hand.
    It needs to be random, because I would like to avoid any number sequence. 001, 002, 003 etc.
    Hope for your understanding, I know it is possible with a small script.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  5. #5
    Pommetje77 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Location
    the Netherlands
    Posts
    37
    Thanks for your reply,
    I have seen that code, but it is just giving me random numbers and the code does not check if number already exists.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    That will be part of your job, and it seems you alluded to that
    I want to control the number myself by a script, so I can adapt it in the future.

    Getting a random number is one step, ensuring it isn't duplicated is another.
    You'll have to check for that.

  7. #7
    Pommetje77 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Location
    the Netherlands
    Posts
    37
    Ok,
    I already have something working, however it is extremely slow.
    I was searching for a solution and than I came across someone who told me to use infinite loop, but I don't know anything about infinite loop.
    So I try this forum.

    The following code is working, however it is extremely slow.

    On Error GoTo Err_Handler
    Dim intHighNumber As Integer
    Dim intLownumber As Integer
    Dim intnumber As Integer
    Dim i As Integer


    ' Set bandwidth and generate number
    intHighNumber = 999
    intLownumber = 100


    For i = 100 To 999
    Randomize
    intnumber = Int((intHighNumber - intLownumber + 1) * Rnd + intLownumber)

    ' Check if number exists
    If DLookup("[Supplier Code]", "Suppliers", "txtSupplierCode='" & intnumber & "'") Then

    'Continue generating
    Randomize
    intnumber = Int((intHighNumber - intLownumber + 1) * Rnd + intLownumber)
    Else

    Me.txtSupplierCode = intnumber

    End If
    Next

    What am I doing wrong here?
    Please advice!

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tell us what you think that code is doing.

    You have chosen a set up that restricts your supplier number to 100---999.
    Why convert an Integer to text?
    You might research DCount()

    If Dcount("yourfieldName", "yourTablename","yourfieldValue =" & yourRandomValue) > 0 then
    ---- you already have that number assigned
    ----- So here you would have to get another random number a test if it was alredy assigned.
    else
    Assign the number, It isn't a duplicate
    end if

  9. #9
    Pommetje77 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Location
    the Netherlands
    Posts
    37
    Ok,

    The following code works like a charm!
    I used your link (post 4) with function, then I made the following script under the generate button:

    Dim a As Integer
    Dim z As Integer
    Dim TempV As String


    a = 100
    z = 999


    TempV = randomNumber(a, z)
    If DLookup("[Supplier Code]", "Suppliers", "nSupplierCode=" & TempV) > 0 Then
    TempV = randomNumber(a, z)
    Else
    Me.nSupplierCode = TempV
    End If
    End Sub

    And yes, I restrict the number value between 100 and 999, While I would like to avoid 001, 005 numbers.
    Do you think I can improve the script?
    But now it gets complicated with my letter code generator for generating lettercodes like AX, DT, DU, etc

    I would like to thank you for your support!

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Glad you have it working. Looks good.

    Update: Here is a small function to get alpha characters

    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : randomalpha
    ' Author    : mellon
    ' Date      : 12/10/2015
    ' Purpose   : Returns random alpha characters based on your HowManyChars value
    '---------------------------------------------------------------------------------------
    '
    Function randomalpha(HowManyChars As Integer) As String
              Dim a As Integer
              Dim z As Integer, i As Integer
              Dim str As String
              Dim ralpha As Variant
              Dim Result As String
    10       On Error GoTo randomalpha_Error
    
    20        a = 1
    30        z = 26
    40        str = "A B C D E F G H I J K L M N O P Q R S T U V W X Y Z"
    50        ralpha = Split(str, " ")
    
    60        For i = 1 To HowManyChars
    70            Result = Result & ralpha(randomNumber(a, z) - 1)
                   'Debug.Print Result
    80        Next i
    90        randomalpha = Result
    
    100      On Error GoTo 0
    110      Exit Function
    
    randomalpha_Error:
    
    120       MsgBox "In Line " & Erl & "  Error " & Err.number & " (" & Err.Description & ") in procedure randomalpha  of Module AccessMonster"
    End Function
    and a test routine

    Code:
    Sub testRandomAlpha()
     Debug.Print randomalpha(4)
    End Sub

  11. #11
    Pommetje77 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Location
    the Netherlands
    Posts
    37
    Help Orange, thank you very much for your update!
    I will test it tomorrow, and see if I can get it working.

    will come back to you asap

  12. #12
    Pommetje77 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Location
    the Netherlands
    Posts
    37
    Hello Orange,

    I would like to make use of your experience again

    I used the function code under post 10 and put a generator button (cmdGenerateCode) on the form with the following script:

    Private Sub cmdGenerateCode_Click()
    Dim TempC As String


    TempC = randomalpha(2)
    If DLookup("[Product Code]", "Products", "txtProductCode ='" & TempC & "'") Then
    TempC = randomalpha(2)
    Else
    Me.txtProductCode = TempC
    End If
    End Sub

    The script is working, however I had some doubts if the Dcount was working, so I changed the function to make letter codes with a maximum of 2 variables AA, BB, AB, BA.
    And I generate a few codes and it gave me duplicates, so my doubts were right.
    Can you please tell me what I am doing wrong here?

  13. #13
    Pommetje77 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Location
    the Netherlands
    Posts
    37
    Hello Orange,
    While the script under post 12 gives me duplicates, I analyzed the script for random numbers (post 9) again.
    When I change the bandwidth a=10 and z=20 and fill the table with some numbers between 10 and 20, it can be that the script is providing me with a number between 10 and 20 which already have been used.
    So even this script is giving me duplicates
    What is going on?

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Why do you think that, if you generate 10 random numbers, there will not be any duplicates?

    A random number is a just a number. Consider a "box" of numbers between 10 and 20, you reach in and pick a number at random. You put the number back in the box, shake it up and pick another individual number. Must it be different than the number you picked previously? If so, why?

    If you do not want duplicates, then you could
    a) use incremental /sequential numbers, or
    b) pick a random number, check to ensure it is unique from other picks, or pick another until you get a unique pick.

    I don't think there is anything special going on.

  15. #15
    Pommetje77 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Location
    the Netherlands
    Posts
    37
    Well, the script should have checked the table on the random picked number/letter code.
    If this random picked number/letter code is in the table, it shall continue picking a number, until the script picked a number which is not in the table, right?

    So, either the script is not correct, or I simply don't get it :-)
    If there are no numbers left to be picked, I would like to get informed about this. I expect an error or a message box.
    But it simply give me a duplicate number/letter code.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-23-2013, 10:15 AM
  2. Problem with code of random assignment of records
    By lios1984 in forum Programming
    Replies: 13
    Last Post: 04-17-2012, 11:02 PM
  3. Replies: 1
    Last Post: 03-01-2012, 04:35 AM
  4. Unique Letter Code For Each Row.....
    By anilytics in forum Forms
    Replies: 7
    Last Post: 02-28-2012, 04:24 PM
  5. Access generating random numbers
    By dama in forum Forms
    Replies: 1
    Last Post: 02-19-2012, 06:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums