Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-18-2017, 03:23 AM   #1
world33
Newly Registered User
 
Join Date: Oct 2006
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
world33 is on a distinguished road
VBA module to generate Unique random alphanumeric strings of random length

I have been searching for a VBA module to generate Unique random alphanumeric strings of random length (or length between X and Y number of characters)

I found the following one:


Quote:
Public Function GenerateUniqueSequence(numberOfCharacters As Integer) As String

Dim random As String ' * 8 'fixed length string with 8 characters
Dim j As Integer
Dim ch As Integer ' each character

random = ""

For j = 1 To numberOfCharacters
random = random & GenerateRandomAlphaNumericCharacter
Next

GenerateUniqueSequence = random

End Function

Public Function GenerateRandomAlphaNumericCharacter() As String

'Numbers : 48 is '0', 57 is '9'
'LETTERS : 65 is 'A', 90 is 'Z'
'letters : 97 is 'a', 122 is 'z'

GenerateRandomAlphaNumericCharacter = ""

Dim i As Integer

Randomize
i = (Rnd() * 2) + 1 'One chance out of 3 to choose one of 3 catégories

Randomize
Select Case i
Case 1 'Numbers
GenerateRandomAlphaNumericCharacter = Chr(Rnd() * 9 + 48)
Case 2 'LETTERS
GenerateRandomAlphaNumericCharacter = Chr(Rnd() * 25 + 65)
Case 3 'letters
GenerateRandomAlphaNumericCharacter = Chr(Rnd() * 25 + 97)
End Select

End Function
Source: https://stackoverflow.com/questions/...-in-text-field


The only issue I have with this function is that it automatically changes random string every time I click on the generated random string in the query Datasheet View. I would like the strings to be static not dynamic. Any way to modify the above module so that once the random strings are generate they cannot change?

Alternatively is there any other VBA module that allows me to generate Unique random alphanumeric strings of random length ?

Thank you

world33 is offline   Reply With Quote
Old 10-18-2017, 03:39 AM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,581
Thanks: 68
Thanked 2,754 Times in 2,638 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: VBA module to generate Unique random alphanumeric strings of random length

assign the value generated by the
random generator to a Public Variable or
Tempvars. then you will just check this
variable on the random generator and
exit the function if it does.
you must find a way to set back the
value to empty string on your code.
something like this:

Add a Standard Module on your VBE and this:

Public gstrRandomNumber As String


on your random generator:

Public Function GenerateUniqueSequence(numberOfCharacters As Integer) As String
...
...
If Len(gstrRandomNumber) > 0 Then
GenerateUniqueSequence = gstrRandomNumber
Exit Function
End If

' rest of code the generage the number
..
..

gstrRandomNumber = random
GenerateUniqueSequence
End Function


'* now you need a way to reset to blank
the gstrRandomNumber. If you use form
(datasheet, continuous, single, etc)
you can initialize this variable on its
Open or Load Event:

Private Sub Form_Open(Cancel As Integer)
gstrRandomNumber = ""
End Sub
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-18-2017, 05:50 AM   #3
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,385
Thanks: 105
Thanked 2,058 Times in 2,002 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: VBA module to generate Unique random alphanumeric strings of random length

world33,

Isn't the fact that the "random numbers/string" changes each time - the whole idea of random?
Do you want repeatable or constants?

Note: It is the Randomize in the function that ensures the seed is reset on each
execution. You could try to comment out that line and experiment.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 10-18-2017, 06:06 AM   #4
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,495
Thanks: 12
Thanked 2,305 Times in 2,256 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: VBA module to generate Unique random alphanumeric strings of random length

To further jdraw's comment and set it in VBA, here's a function that will return a random, static string:

Code:
Public Function GenerateUniqueSequence() As String

GenerateUniqueSequence = "1msdlkf87JFEY6509GVEf"

End Function
If you want something else, perhaps you could explain the ultimate goal. Take us a step higher and tell us the ends this random string is the means to.
plog is online now   Reply With Quote
Old 10-18-2017, 01:00 PM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,387
Thanks: 15
Thanked 1,623 Times in 1,541 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: VBA module to generate Unique random alphanumeric strings of random length

Don't call the functions if you don't want to change the values.

What event are you using to call the functions?
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-18-2017, 01:55 PM   #6
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
The Scripting.FileSystemObject exposes a .GetTempName method, which returns a random filename. If you strip off the garbage, you can use the characters it returns and do something like...
Code:
Public Function GetRandomName(Optional Length As Byte = 10) As String
    Dim i As Integer
    Dim tmp As String
    
    With CreateObject("Scripting.FileSystemObject") 'late bound
        For i = 1 To CInt((Length / 5) + 0.5)       'each call to .GetTempName will give us 5 chars
            tmp = tmp & Mid(.GetTempName, 4, 5)     'only keep the 5 chars we need
        Next i
    End With
    GetRandomName = Left(tmp, Length)               'pare it down to the requested length
End Function
Just an idea you might be able to use...
Mark
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 10-18-2017, 02:36 PM   #7
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,112
Thanks: 20
Thanked 405 Times in 396 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: VBA module to generate Unique random alphanumeric strings of random length

I think the solution to the question you are trying to ask is in
Quote:
every time I click on the generated random string in the query Datasheet View
Are you defaulting a field value to your random string that you then want to remain consistent? Just to make sure, you are fine with there being duplicate values?

If yes, then when you first enter the record you would want to do something like this:

Code:
DIM asString as String
DIM afBreak as Integer

afBreak = 0
IF File.Field = NULL then 
   DO
      asString =  GenerateUniqueSequence(<NUMBER>)
      IF DLookup("[Field]", "File", "[Field] = " & asString) = NULL Then
         File.Field = asString
         afBreak = 1
      end If
   Until afBreak = 1
End IF
Please note, I haven't tried this myself as I've not needed to.

Mark_ is offline   Reply With Quote
Old 10-18-2017, 02:40 PM   #8
world33
Newly Registered User
 
Join Date: Oct 2006
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
world33 is on a distinguished road
Re: VBA module to generate Unique random alphanumeric strings of random length

Thanks all for the feedback.

I use the following string to call the module in the query Universities by Name:

UpdateID: GenerateUniqueSequence(Rnd([SITES]![ID])*15+15)

I attach 2 screenshots for those that did not understand the issue. I do need unique random strings but I do not need them to change once they are populated in a query just because I click on the cell where they are shown.

I have to modify somehow the function or the way I call it so that this occurrence does not happen anymore.

Thanks again for any help
world33 is offline   Reply With Quote
Old 10-18-2017, 02:44 PM   #9
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,112
Thanks: 20
Thanked 405 Times in 396 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: VBA module to generate Unique random alphanumeric strings of random length

world33

Only assign a value to your UpdateID if UpdateID = Null. If it does not exist, make one. If it does exist, leave it alone.

Where do you have your code currently? What event fills UpdateID?
Mark_ is offline   Reply With Quote
Old 10-18-2017, 08:49 PM   #10
world33
Newly Registered User
 
Join Date: Oct 2006
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
world33 is on a distinguished road
Re: VBA module to generate Unique random alphanumeric strings of random length

Hi Mark,

The UpdateID is a calculated query field using this formula:
UpdateID: GenerateUniqueSequence(Rnd([SITES]![ID])*15+15)
It is not present in any table so there is no point, correct me if I am wrong, to have UpdateID = Null . It is a complete new query field.
The function is in a Module.
world33 is offline   Reply With Quote
Old 10-19-2017, 12:32 AM   #11
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,481
Thanks: 172
Thanked 1,752 Times in 1,720 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: VBA module to generate Unique random alphanumeric strings of random length

As it is calculated to be random at the time the query is run, any time you amend the data the function will re-run.

That is how a calculated field works. Think about it logically - if your calculated field was LineTotal: Qty * UnitPrice and you clicked in the Linetotal control, if it didn't update to the current value it would be useless.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 10-19-2017, 04:29 AM   #12
world33
Newly Registered User
 
Join Date: Oct 2006
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
world33 is on a distinguished road
Re: VBA module to generate Unique random alphanumeric strings of random length

So is there any modification I can make to keep the same values?
world33 is offline   Reply With Quote
Old 10-19-2017, 04:37 AM   #13
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,581
Thanks: 68
Thanked 2,754 Times in 2,638 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: VBA module to generate Unique random alphanumeric strings of random length

like i suggested, create a Public variable.
use Form (datasheet) to display your Query.

So everytime you open the Form, you Reset the
variable:

Private Sub Form_Open(Cancel As Integer)
gstrRandomNumber = ""
End Sub


'**
'**
follow the code from the first post (#2).
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-19-2017, 07:47 AM   #14
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,112
Thanks: 20
Thanked 405 Times in 396 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: VBA module to generate Unique random alphanumeric strings of random length

Quote:
Originally Posted by world33 View Post
So is there any modification I can make to keep the same values?
If you want the same value for a given ID, store it in a file. Queries are temporary images of data, not a place to store data.

The alternative is to create a function that will ALWAYS generate the same value for a given ID, but this would not then be a "Random" value.

From your description at top, I had thought it was a field in your table. Using your code as a default value (or filling it in if null when you enter the update form) would allow you to keep the same "UpdateID" for any given ID. Likewise if this would need to be unique (and associating it with a given ID lends me to believe this is what you are looking for) you could us DLookup to then make sure it is truly a unique random value that you are storing.

Mark_ is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Generate random and unique number MrHans Modules & VBA 12 05-10-2016 02:15 AM
[SOLVED] Generate Random No kzatakia Modules & VBA 6 04-19-2016 03:13 AM
Generate Table with a Random Name nashken Queries 10 10-21-2015 03:33 AM
Standard length on random autonumber midastouch1 Tables 4 05-31-2014 01:56 PM
Generate a Random Number srbooth Queries 9 12-17-2008 12:55 PM




All times are GMT -8. The time now is 10:39 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World