Random Generator

[Reed]

I Have:

tblNumbers
--------------
fldNumbersID AutoNumber
fldNumbers Text index no duplicates

What i need to create is, a random generator that would
take alphanumeric characters and generate these for me and
put them in fldNumbers

I need to be able to pick at the time how many numbers to
generate (qty) and the length, i.e. 6 or 10, etc, and when
it incounters a duplicate, that it ignores it and contiues
without putting the duplicate in the field.

Has anyone done this?

Thanks in advance for reading this post.

Reed

 

[Wayne Morgan]

This appears to do what you ask. You would call it with the 2 parameters you asked for.
Example:
TestRandomEntries 10, 10
will give 10 entries that are 10 characters long.


Public Sub TestRandomEntries(intQty As Integer, intLength As Integer)
On Error GoTo HandleError

Dim intValue As Integer, strValue As String, intCount As Integer
Dim db As DAO.Database, rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Table9", dbOpenTable)

Randomize
Const DUPLICATE_INDEX = 3022
intCount = 0
strValue = ""
Do Until intCount = intQty
Do Until Len(strValue) = intLength
'Alpha Numeric characters are ASCII 48-57, 65-90, and 97-122
'Formula from the help file
'Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
intValue = Int((122 - 48 + 1) * Rnd + 48)
Select Case intValue
Case 48 To 57, 65 To 90, 97 To 122
strValue = strValue & Chr(intValue)
Case Else
End Select
Loop
With rst
.AddNew
![fldNumbers] = strValue 'Add data.
.Update 'Save changes.
End With
'Increment our counter to see
'how many we have added
intCount = intCount + 1
'Start a new string
strValue = ""
Loop

CleanUp:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

HandleError:
If Err.Number = DUPLICATE_INDEX Then
'Only count successful additions
intCount = intCount - 1
Resume Next
End If
MsgBox Err.Number & vbCrLf & Err.Description, , "Error"
Resume CleanUp

End Sub

 

[Reed]

So how would I create the form to put in the values of Qty
and Length? I assume that the code below would attach to a
form event.

Thank you

Reed

-----Original Message-----
This appears to do what you ask. You would call it with

the 2 parameters you asked for.

Example:
TestRandomEntries 10, 10
will give 10 entries that are 10 characters long.


Public Sub TestRandomEntries(intQty As Integer, intLength As Integer)
On Error GoTo HandleError

Dim intValue As Integer, strValue As String, intCount As Integer
Dim db As DAO.Database, rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Table9", dbOpenTable)

Randomize
Const DUPLICATE_INDEX = 3022
intCount = 0
strValue = ""
Do Until intCount = intQty
Do Until Len(strValue) = intLength
'Alpha Numeric characters are ASCII 48-57, 65-90, and 97-122
'Formula from the help file
'Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
intValue = Int((122 - 48 + 1) * Rnd + 48)
Select Case intValue
Case 48 To 57, 65 To 90, 97 To 122
strValue = strValue & Chr(intValue)
Case Else
End Select
Loop
With rst
.AddNew
![fldNumbers] = strValue 'Add data.
.Update 'Save changes.
End With
'Increment our counter to see
'how many we have added
intCount = intCount + 1
'Start a new string
strValue = ""
Loop

CleanUp:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

HandleError:
If Err.Number = DUPLICATE_INDEX Then
'Only count successful additions
intCount = intCount - 1
Resume Next
End If
MsgBox Err.Number & vbCrLf & Err.Description, , "Error"
Resume CleanUp

End Sub


--
Wayne Morgan
Microsoft Access MVP


.

 

[Wayne Morgan]

Open a new form in design view. Place a label near the top that says what to do. Place 2
text boxes on the form, one for length and one for quantity. Put a command button on the
form, set its Enabled property to No.

In the AfterUpdate event of each textbox, check to see if there is a valid value in each
textbox. If there is, set the Enabled property of the button to Yes. In the OnClick event
of the button you would use something similar to:

TestRandomEntries Me.txtTextbox1, Me.txtTextbox2

This will put the entries into the table. What you do from here is up to you. Also, you
didn't say if you wanted the table cleared before each time you create entries. At the
moment, it is not being cleared. The new entries are added after the previous ones.

--
Wayne Morgan
Microsoft Access MVP

So how would I create the form to put in the values of Qty
and Length? I assume that the code below would attach to a
form event.

Thank you

Reed

-----Original Message-----
This appears to do what you ask. You would call it with

the 2 parameters you asked for.

Example:
TestRandomEntries 10, 10
will give 10 entries that are 10 characters long.


Public Sub TestRandomEntries(intQty As Integer, intLength As Integer)
On Error GoTo HandleError

Dim intValue As Integer, strValue As String, intCount As Integer
Dim db As DAO.Database, rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Table9", dbOpenTable)

Randomize
Const DUPLICATE_INDEX = 3022
intCount = 0
strValue = ""
Do Until intCount = intQty
Do Until Len(strValue) = intLength
'Alpha Numeric characters are ASCII 48-57, 65-90, and 97-122
'Formula from the help file
'Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
intValue = Int((122 - 48 + 1) * Rnd + 48)
Select Case intValue
Case 48 To 57, 65 To 90, 97 To 122
strValue = strValue & Chr(intValue)
Case Else
End Select
Loop
With rst
.AddNew
![fldNumbers] = strValue 'Add data.
.Update 'Save changes.
End With
'Increment our counter to see
'how many we have added
intCount = intCount + 1
'Start a new string
strValue = ""
Loop

CleanUp:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

HandleError:
If Err.Number = DUPLICATE_INDEX Then
'Only count successful additions
intCount = intCount - 1
Resume Next
End If
MsgBox Err.Number & vbCrLf & Err.Description, , "Error"
Resume CleanUp

End Sub


--
Wayne Morgan
Microsoft Access MVP


.

 

[Mike Painter]

I Have:

tblNumbers
--------------
fldNumbersID AutoNumber
fldNumbers Text index no duplicates

What i need to create is, a random generator that would
take alphanumeric characters and generate these for me and
put them in fldNumbers

I need to be able to pick at the time how many numbers to
generate (qty) and the length, i.e. 6 or 10, etc, and when
it incounters a duplicate, that it ignores it and contiues
without putting the duplicate in the field.

Has anyone done this?

Random numbers may have duplicates. If you eliminate them they *ARE NOT*
random.

 

[Reed]

This works very well.

Thank you for your help.

Reed

-----Original Message-----
Thank you for the reply.

I will try this and let you know.

Thank you again for your help

Reed

-----Original Message-----
Open a new form in design view. Place a label near the

top that says what to do. Place 2

text boxes on the form, one for length and one for

quantity. Put a command button on the

form, set its Enabled property to No.

In the AfterUpdate event of each textbox, check to see

if
there is a valid value in each

textbox. If there is, set the Enabled property of the

button to Yes. In the OnClick event

of the button you would use something similar to:

TestRandomEntries Me.txtTextbox1, Me.txtTextbox2

This will put the entries into the table. What you do

from here is up to you. Also, you

didn't say if you wanted the table cleared before each

time you create entries. At the

moment, it is not being cleared. The new entries are added after the previous ones.

--
Wayne Morgan
Microsoft Access MVP

So how would I create the form to put in the values of Qty
and Length? I assume that the code below would attach to a
form event.

Thank you

Reed



-----Original Message-----
This appears to do what you ask. You would call it with
the 2 parameters you asked for.
Example:
TestRandomEntries 10, 10
will give 10 entries that are 10 characters long.


Public Sub TestRandomEntries(intQty As Integer, intLength
As Integer)
On Error GoTo HandleError

Dim intValue As Integer, strValue As String, intCount As
Integer
Dim db As DAO.Database, rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Table9", dbOpenTable)

Randomize
Const DUPLICATE_INDEX = 3022
intCount = 0
strValue = ""
Do Until intCount = intQty
Do Until Len(strValue) = intLength
'Alpha Numeric characters are ASCII 48-57, 65- 90,
and 97-122
'Formula from the help file
'Int((upperbound - lowerbound + 1) * Rnd +
lowerbound)
intValue = Int((122 - 48 + 1) * Rnd + 48)
Select Case intValue
Case 48 To 57, 65 To 90, 97 To 122
strValue = strValue & Chr(intValue)
Case Else
End Select
Loop
With rst
.AddNew
![fldNumbers] = strValue 'Add data.
.Update 'Save changes.
End With
'Increment our counter to see
'how many we have added
intCount = intCount + 1
'Start a new string
strValue = ""
Loop

CleanUp:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

HandleError:
If Err.Number = DUPLICATE_INDEX Then
'Only count successful additions
intCount = intCount - 1
Resume Next
End If
MsgBox Err.Number & vbCrLf & Err.Description, , "Error"
Resume CleanUp

End Sub


--
Wayne Morgan
Microsoft Access MVP


.

.

.