Solved

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

Posted on 2016-10-13
23
45 Views
Last Modified: 2016-10-16
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
Comment
Question by:Dustin Stanley
  • 11
  • 10
23 Comments
 

Author Comment

by:Dustin Stanley
Comment Utility
I honestly don't know. It auto filled the Tags for me.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
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
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
How many records do you anticipate being created?  Total?  Per year?
0
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
Comment Utility
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
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
@Gustav

Please check your algorithm.  I think you are missing numeric digits.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
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
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
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
 
LVL 45

Accepted Solution

by:
aikimark earned 250 total points
Comment Utility
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
 

Author Comment

by:Dustin Stanley
Comment Utility
Thank you I will give these a try tonight.
0
 

Author Comment

by:Dustin Stanley
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 45

Expert Comment

by:aikimark
Comment Utility
add the routine to a module
0
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
It is a function.  You must pass it a numeric length parameter.  It returns a string.
0
 

Author Comment

by:Dustin Stanley
Comment Utility
Thank you. Sorry I have busy. I will try this out.
0
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 

Author Closing Comment

by:Dustin Stanley
Comment Utility
You guys are AWESOME!

Private Sub Form_BeforeInsert(Cancel As Integer)
Operator = "11"
Forms!Form1!IDNm = UniqueString(Operator)
End Sub
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
You can also use the function in a query.
0
 

Author Comment

by:Dustin Stanley
Comment Utility
Could you give me an example please? Food for thought. I plan on using this for different things like reports. thanks.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
In the select clause of you query, a column could be something like this:
uniquestring(11) As Key
0
 

Author Comment

by:Dustin Stanley
Comment Utility
Great thanks!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now