[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

MS Access Random String Generator New Unique In a Specific Form Control On New Record

I need Access to generate a random Unique String in a specific form control when adding a new record.

String Requirements:

  • 11 Length
  • Alphanumeric (Letters and Numbers NO SPECIAL CHARACTERS)
  • Unique 1 Of A Kind
  • Not Containing the Letters I, L, O
  • UpperCase Only

Example: 195Z26Z8MFX

Thank You!
0
Dustin Stanley
Asked:
Dustin Stanley
  • 11
  • 10
2 Solutions
 
Dustin StanleyEntrepreneurAuthor Commented:
I honestly don't know. It auto filled the Tags for me.
0
 
aikimarkCommented:
Before you move forward with this, you should be aware of weaknesses and limitations in the VB pseudo-random number generator. I have written an article on the subject:
https://www.experts-exchange.com/articles/11114/An-Examination-of-Visual-Basic's-Random-Number-Generation.html

How distinct should these strings be?  For instance, if two consecutive strings only differed by their first or last character, would that cause you any problems?

How often are these values generated? Sub-second?
0
 
aikimarkCommented:
How many records do you anticipate being created?  Total?  Per year?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Dustin StanleyEntrepreneurAuthor Commented:
How distinct should these strings be?  For instance, if two consecutive strings only differed by their first or last character, would that cause you any problems?

No this would not matter as long as they are different.

How many records do you anticipate being created?  Total?  Per year?

10,000 Rough guess
0
 
Gustav BrockCIOCommented:
This function will do that:
Public Function StrRandom(ByVal lngLen As Long) As String

' Create fixed length string of random characters.
' Will generate about 512K per second.
'
' 2002-02-02. Cactus Data ApS, CPH

    Dim StrRnd        As String
    Dim lngN          As Long
    Dim strChar       As String
      
    Randomize
    lngLen = Abs(lngLen)
    ' Create string of zeroes, lngLen long.
    StrRnd = String(lngLen, "0")
    ' Perform hi-speed filling of string with random character string.
    While lngN < lngLen
        strChar = Chr(65 + Rnd * (90 - 65))
        Select Case strChar
            Case "I", "L", "O"
                ' Ignore.
            Case Else
                Mid(StrRnd, lngN + 1) = strChar
                ' Calculate entry position for next substring.
                lngN = lngN + 1
        End Select
    Wend
  
    StrRandom = StrRnd
  
End Function

Open in new window

So:

    RandomID = StrRandom(11)

-> JRWDKMTXFTF

/gustav
0
 
aikimarkCommented:
@Gustav

Please check your algorithm.  I think you are missing numeric digits.
0
 
aikimarkCommented:
Ignoring the weakness of the VB PRNG, here is one solution.  You should have a unique index on the field to prevent duplicates.
Function Q_28976346(ByVal parmLen) As String
    Const cAlphabet = "ABCDEFGHJKMNPQRSTUVWXYZ0123456789"
    Const AlphabetLen = 33
    Dim lngLoop As Long
    Q_28976346 = String(parmLen, " ")
    For lngLoop = 1 To parmLen
        Mid(Q_28976346, lngLoop, 1) = Mid(cAlphabet, Int(Rnd * AlphabetLen) + 1, 1)
    Next
End Function

Open in new window

0
 
aikimarkCommented:
This version of the code will produce strings that are more likely to be unique.
Function Q_28976346(ByVal parmLen) As String
    Const cAlphabet = "ABCDEFGHJKMNPQRSTUVWXYZ0123456789"
    Const AlphabetLen = 33
    Dim lngLoop As Long
    Dim lngOffset As Long
    lngOffset = Timer       'can go as high as Timer * 100
    For lngLoop = 1 To lngOffset
        Rnd
    Next
    
    Q_28976346 = String(parmLen, " ")
    For lngLoop = 1 To parmLen
        Mid(Q_28976346, lngLoop, 1) = Mid(cAlphabet, Int(Rnd * AlphabetLen) + 1, 1)
    Next
End Function

Open in new window

If you aren't likely to invoke this more frequently than 1/second, you can retain the current lngOffset value-setting statement.

Again, unique indexes are required to prevent duplicates.
0
 
aikimarkCommented:
Given the weaknesses in the VB PRNG, I've grown fond of this approach.  Here, I get one or more GUID values and use that in stead of the Rnd values.  Since these are GUIDS, I don't think you need to have a unique index, although it wouldn't hurt to have that uniqueness assurance.
Function UniqueString(ByVal parmLen) As String
    Const cAlphabet = "ABCDEFGHJKMNPQRSTUVWXYZ0123456789"
    Const AlphabetLen = 33
    Dim lngLoop As Long
    Dim lngOffset As Long
    Dim lngPosn As Long
    Dim GUID As String
    Dim GUID_Trailer As String
    
    Do
        GUID = GUID & Replace(Mid(CreateObject("scriptlet.typelib").GUID, 2, 36), "-", vbNullString)
    Loop Until Len(GUID) >= parmLen
    
    UniqueString = String(parmLen, "*")     'initialize output string
    Select Case Len(GUID)
        Case parmLen
            'convert entire GUID to string
            lngPosn = 1
            For lngLoop = 1 To Len(GUID) Step 2
                lngOffset = "&h" & Mid(GUID, lngLoop, 2)
                Mid(UniqueString, lngPosn, 1) = Mid(cAlphabet, (lngOffset Mod AlphabetLen) + 1, 1)
                lngPosn = lngPosn + 1
            Next
            
        Case Is > parmLen
            'use remaining byte values as increment, mod 256
            GUID_Trailer = Mid(GUID, (parmLen * 2) + 1)
            Do
                GUID_Trailer = GUID_Trailer & GUID_Trailer
            Loop Until Len(GUID_Trailer) >= ((parmLen * 2) + 6)
            
            GUID = Left(GUID, (parmLen * 2))
            
            lngPosn = 1
            For lngLoop = 1 To Len(GUID) Step 2
                lngOffset = CLng("&h" & Mid(GUID, lngLoop, 2)) + CLng("&h" & Mid(GUID_Trailer, lngPosn, 6))
                Mid(UniqueString, lngPosn, 1) = Mid(cAlphabet, (lngOffset Mod AlphabetLen) + 1, 1)
                lngPosn = lngPosn + 1
            Next
        
    End Select
End Function

Open in new window

0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thank you I will give these a try tonight.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Ok Thank you all I am more of a beginner I have only done small coding in VBA so far. How would I insert this code? Would I use a command button with a text box for general use stand alone?  I am familiar with using events and such. I really want this to auto generate in a specific control on my form when a new record is generated such as in the beforeinsert event. Just a little lost on how to use it. I can see the coding used and it looks great THANKS!
0
 
aikimarkCommented:
add the routine to a module
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Ok I apologize now for any lack of ignorance. But I added the code to a module named UniqueString and then in a following Sub put RunUniqueString() But nothing. I know you see the HUGE OBVIOUS error with your expertise but me No! Thank you for your time.

I am not seeing where to place my text box name in the code to see the random string when generated?
0
 
aikimarkCommented:
It is a function.  You must pass it a numeric length parameter.  It returns a string.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thank you. Sorry I have busy. I will try this out.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
JUST BRILLIANT! I have had success with a message box but how would I put this in a specific control on a form to run on the before insert event.

Option Compare Database
Option Explicit
Dim Operator As Integer

Function UniqueString(ByVal parmLen) As String
    Const cAlphabet = "ABCDEFGHJKMNPQRSTUVWXYZ0123456789"
    Const AlphabetLen = 33
    Dim lngLoop As Long
    Dim lngOffset As Long
    Dim lngPosn As Long
    Dim GUID As String
    Dim GUID_Trailer As String
    
    Do
        GUID = GUID & Replace(Mid(CreateObject("scriptlet.typelib").GUID, 2, 36), "-", vbNullString)
    Loop Until Len(GUID) >= parmLen
    
    UniqueString = String(parmLen, "*")     'initialize output string
    Select Case Len(GUID)
        Case parmLen
            'convert entire GUID to string
            lngPosn = 1
            For lngLoop = 1 To Len(GUID) Step 2
                lngOffset = "&h" & Mid(GUID, lngLoop, 2)
                Mid(UniqueString, lngPosn, 1) = Mid(cAlphabet, (lngOffset Mod AlphabetLen) + 1, 1)
                lngPosn = lngPosn + 1
            Next
            
        Case Is > parmLen
            'use remaining byte values as increment, mod 256
            GUID_Trailer = Mid(GUID, (parmLen * 2) + 1)
            Do
                GUID_Trailer = GUID_Trailer & GUID_Trailer
            Loop Until Len(GUID_Trailer) >= ((parmLen * 2) + 6)
            
            GUID = Left(GUID, (parmLen * 2))
            
            lngPosn = 1
            For lngLoop = 1 To Len(GUID) Step 2
                lngOffset = CLng("&h" & Mid(GUID, lngLoop, 2)) + CLng("&h" & Mid(GUID_Trailer, lngPosn, 6))
                Mid(UniqueString, lngPosn, 1) = Mid(cAlphabet, (lngOffset Mod AlphabetLen) + 1, 1)
                lngPosn = lngPosn + 1
            Next
        
    End Select
End Function

Private Sub Command0_Click()
Operator = "11"
MsgBox UniqueString(Operator)
End Sub

Open in new window

0
 
Dustin StanleyEntrepreneurAuthor Commented:
Ok this works so far on click

Private Sub Text2_Click()
Operator = "11"
Forms!UniqueString!Text2 = UniqueString(Operator)
End Sub

Open in new window

0
 
Dustin StanleyEntrepreneurAuthor Commented:
You guys are AWESOME!

Private Sub Form_BeforeInsert(Cancel As Integer)
Operator = "11"
Forms!Form1!IDNm = UniqueString(Operator)
End Sub
0
 
aikimarkCommented:
You can also use the function in a query.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Could you give me an example please? Food for thought. I plan on using this for different things like reports. thanks.
0
 
aikimarkCommented:
In the select clause of you query, a column could be something like this:
uniquestring(11) As Key
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Great thanks!
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 11
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now