Solved

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

Posted on 2016-10-13
23
68 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
ID: 41842994
I honestly don't know. It auto filled the Tags for me.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41842995
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
ID: 41842998
How many records do you anticipate being created?  Total?  Per year?
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:Dustin Stanley
ID: 41843053
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
ID: 41843199
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
ID: 41843412
@Gustav

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

Expert Comment

by:aikimark
ID: 41843425
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
ID: 41843437
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
ID: 41843534
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
ID: 41843540
Thank you I will give these a try tonight.
0
 

Author Comment

by:Dustin Stanley
ID: 41844431
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
 
LVL 45

Expert Comment

by:aikimark
ID: 41844450
add the routine to a module
0
 

Author Comment

by:Dustin Stanley
ID: 41844522
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
ID: 41845135
It is a function.  You must pass it a numeric length parameter.  It returns a string.
0
 

Author Comment

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

Author Comment

by:Dustin Stanley
ID: 41845340
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
ID: 41845346
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
ID: 41845350
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
ID: 41845762
You can also use the function in a query.
0
 

Author Comment

by:Dustin Stanley
ID: 41845783
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
ID: 41845786
In the select clause of you query, a column could be something like this:
uniquestring(11) As Key
0
 

Author Comment

by:Dustin Stanley
ID: 41845787
Great thanks!
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

791 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