505.369.1650 [email protected]

This VBA function uses the Randomize() and Rnd() functions to generate a string of characters with a specific length.  The included Microsoft Access example applies the function to a simple form that can be used to create a password.

When the MS Access database opens, the form that appears looks like this:

Random

Enter a Length, click the Go button, and a random string will appear in the Results.  To use the Results, you may click the Copy button and then paste the results wherever it is needed.

The On Click event for the Go button validates the Length and then calls the Random() function.  The On Click event for the Copy button uses the DoCmd.RunCommand acCmdCopy action to place the Results on the .

The Random() function below uses alphas and numerics in the base character list.  You could also add special characters to the base for variety.

Random() Code:

Public Function Random(RLength As Integer) As String
' This function creates a string of random characters, both numbers
' and alpha, with a length of RLength.  It uses  to seed the Rnd
' function.

' Random() Version 1.0.0
' Copyright © 2009 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected]
' Use (at your own risk) and modify freely as long as proper credit is given.

On Error GoTo Err_Random

    Dim strTemp As String
    Dim intLoop As Integer
    Dim strCharBase As String
    Dim intPos As Integer
    Dim intLen As Integer

    ' Build the base.
    strCharBase = "01234ABCDEFGHIJKLMNOPQRSTUVWXYZ" _
    & "abcdefghijklmnopqrstuvwxyz56789"
    ' Get it's length.
    intLen = Len(strCharBase)

    ' Initialize the results.
    strTemp = String(RLength, "A")

    ' Reset the random seed.
    Rnd -1
    ' Initialize the seed using Timer.
    Randomize (Timer)

    ' Loop until you hit the end of strTemp.  Replace each character
    ' with a character selected at random from strCharBase.
    For intLoop = 1 To Len(strTemp)
        ' Use the Rnd function to pick a position number in strCharBase.
        ' If the result exceeds the length of strCharBase, subtract one.
        intPos = CInt(Rnd() * intLen + 1)
        If intPos > intLen Then intPos = intPos - 1
        ' Now assign the character at that position in the base to the
        ' next strTemp position.
        Mid$(strTemp, intLoop, 1) = Mid$(strCharBase, intPos, 1)
    Next

    ' Return the results.
    Random = strTemp

Exit_Random:
    On Error Resume Next
    Exit Function

Err_Random:
    MsgBox Err.Number & " " & Err.Description, vbCritical, "Random"
    Random = ""
    Resume Exit_Random

End Function

Download Code:

Random.zip

Making Microsoft Access Work

No matter what your MS Access needs are, Carl can provide live help to make sure you get the solution you need.