Rnd function error (Random Number generator)

[SiciliandoBlue]

I have a string of code to create a new table and to populate it with four different randomly generated upper case letters (A-Z), using the Rnd function and then compare the result generated to previously generated letters within the same record. If a duplicate is detected a new letter is then generated and the checking process repeated.
I found that the more records I tried to add to the table the higher the likelihood that the fourth generated letter had already been generated in the first three letter fields (not surprisingly), but unlike the second and third letter checking process (using identical code) the Rnd function would continue to generate the same letter, over and over again and result in an infinite loop causing Access to crash!
I tried resolving the problem by first setting the variable (l4) initially to Null, then to 0 (zero), and adding the Randomize function prior to using the Rnd function, but still Rnd produced the exact same value. I also put in lots of messages to pinpoint exactly where the error was occurring. If anyone can suggest the source of the problem and a solution I may not have a nervous breakdown after all!! The function Code is below...

Thanks, SiciliandoBlue.


Function CreateWorkRateTest()

Dim myDB As DAO.Database
Dim rst As DAO.Recordset
Dim bytCodeCount As Byte
Dim i As Variant, j As Variant, k As Variant, l1 As Variant, l2 As Variant, l3 As Variant, l4 As Variant
Dim strSQL As String, strPrompt As String, strTitle As String

Set myDB = CurrentDb()

myDB.Execute "DROP TABLE CodeTable;"

myDB.Execute "CREATE TABLE CodeTable (CodeID BYTE, Letter1 TEXT(1), Number1 TEXT(1), Shape1 TEXT(1), " & _
"Letter2 TEXT(1), Number2 TEXT(1), Shape2 TEXT(1));"

myDB.Execute "ALTER TABLE CodeTable ADD COLUMN Letter3 TEXT(1);"
myDB.Execute "ALTER TABLE CodeTable ADD COLUMN Number3 TEXT(1);"
myDB.Execute "ALTER TABLE CodeTable ADD COLUMN Shape3 TEXT(1);"
myDB.Execute "ALTER TABLE CodeTable ADD COLUMN Letter4 TEXT(1);"
myDB.Execute "ALTER TABLE CodeTable ADD COLUMN Number4 TEXT(1);"
myDB.Execute "ALTER TABLE CodeTable ADD COLUMN Shape4 TEXT(1);"

strSQL = "SELECT * FROM CodeTable;"

Set rst = myDB.OpenRecordset(strSQL, dbOpenDynaset)

On Error GoTo ErrorCode

strTitle = "Number of Codes"
strPrompt = "Enter the number of Codes to be generated"
bytCodeCount = InputBox(strPrompt, strTitle, 20) 'Prompt for the number of Codes to be generated (20)

For i = 1 To bytCodeCount
rst.AddNew
rst("CodeID") = i

Randomize
k = Int((26 * Rnd) + 1) ' Generate random value between 1 and 26.
l1 = k + 64
'MsgBox "l1 = " & l1

k = Int((26 * Rnd) + 1) ' Generate random value between 1 and 26.
l2 = k + 64
Do Until l2 <> l1 ' loop if a duplicate letter is generated
Randomize
k = Int((26 * Rnd) + 1) ' Generate new random value between 1 and 26.
l2 = k + 64
Loop
'MsgBox "l2 = " & l2

k = Int((26 * Rnd) + 1) ' Generate random value between 1 and 26.
l3 = k + 64
Loop1:
If l3 = l2 Then ' loop if a duplicate letter is generated
MsgBox "l3 = l2 (" & l3 & ")"
Randomize
k = Int((26 * Rnd) + 1) ' Generate new random value between 1 and 26.
l3 = k + 64
MsgBox "New l3 value = " & l3
GoTo Loop1
End If
If l3 = l1 Then ' loop if a duplicate letter is generated
MsgBox "l3 = l1 (" & l3 & ")"
Randomize
k = Int((26 * Rnd) + 1) ' Generate new random value between 1 and 26.
l3 = k + 64
MsgBox "New l3 value = " & l3
GoTo Loop1
End If

'MsgBox "l3 = " & l3

k = Int((26 * Rnd) + 1) ' Generate random value between 1 and 26.
l4 = k + 64
Loop2:
If l4 = l3 Then ' loop if a duplicate letter is generated
MsgBox "l4 = l3 (" & l4 & ")"
k = 0
Randomize
k = Int((26 * Rnd) + 1) ' Generate new random value between 1 and 26.
l3 = k + 64
MsgBox "New l4 value = " & l4
GoTo Loop2
End If
If l4 = l2 Then ' loop if a duplicate letter is generated
MsgBox "l4 = l2 (" & l4 & ")"
k = 0
Randomize
k = Int((26 * Rnd) + 1) ' Generate new random value between 1 and 26.
l3 = k + 64
MsgBox "New l4 value = " & l4
GoTo Loop2
End If
If l4 = l1 Then ' loop if a duplicate letter is generated
MsgBox "l4 = l1 (" & l4 & ")"
k = 0
Randomize
k = Int((26 * Rnd) + 1) ' Generate new random value between 1 and 26.
l3 = k + 64
MsgBox "New l4 value = " & l4
GoTo Loop2
End If

'MsgBox "l4 = " & l4

MsgBox "l1 = " & l1 & Chr(13) & _
"l2 = " & l2 & Chr(13) & _
"l3 = " & l3 & Chr(13) & _
"l4 = " & l4
rst("Letter1") = Chr(l1) 'Generates a letter between A and Z


rst("Letter2") = Chr(l2) 'Generates a letter between A and Z
rst("Letter3") = Chr(l3) 'Generates a letter between A and Z
rst("Letter4") = Chr(l4) 'Generates a letter between A and Z
rst.Update
Next i

i = 0
Set rst = Nothing
Set myDB = Nothing

DoCmd.OpenTable "CodeTable"

Exit Function

ErrorCode:

MsgBox Err.Description

End Function

[JamesDeckert]

What confusing code and goto's
I ran it until I get the same number repeatedly.
It is in the following block of code

Loop2:
If l4 = l3 Then ' loop if a duplicate letter is generated
MsgBox "l4 = l3 (" & l4 & ")"
k = 0
Randomize
k = Int((26 * Rnd) + 1) ' Generate new random value between 1 and 26.
l3 = k + 64
MsgBox "New l4 value = " & l4
GoTo Loop2
End If
If l4 = l2 Then ' loop if a duplicate letter is generated
MsgBox "l4 = l2 (" & l4 & ")"
k = 0
Randomize
k = Int((26 * Rnd) + 1) ' Generate new random value between 1 and 26.
l3 = k + 64
MsgBox "New l4 value = " & l4
GoTo Loop2

it returns to the label Loop2 which bypasses the first 'If l4 = l3' and goes into 'If l4 = l2' which recalculates l3.
So I don't know if this is supposed to calculate l4 instead or what. But the infinite loop happens because once they are the same you're not changing anything which is being checked by the second If statement.

This code would be more understandable if the repeated line were placed in a function.
Also using a loop would be better than goto's to build loops.

[orange]

In addition to James' comment, the code would be easier to follow if you used some consistent indentation/formatting.
There is a free utility called Smart Indenter for Access which will do this for you.

Also, when posting an issue/opportunity, try putting yourself in the readers position. We don't know you, the issue, and, unless you are specific -with examples; plain English terms, we an only guess at what you are trying to do. James has done some good debugging to track down the issue.

[SiciliandoBlue]

James, thanks! I made the mistake of copying the code from Loop1 into Loop2 and adding the extra "If l4 = l3 Then..." check, but forgot to then edit the "l" variable to be re-randomised for l3 to l4! So, in answer to your question, I don't know if this is supposed to calculate l4 instead.... Yes, it is!

I originally used Do while ... Loop, but changed to the If Then function when the errors occurred.

I'm not sure how to use a function to replace the repeated line?? Always keen to learn though if you can give me a pointer?

Thanks!

[orange]

Steve Bishop has some great tutorials on youtube. See if this helps

[SiciliandoBlue]

excellent! thanks very much, yes, that Steve Bishop tutorial was very informative. I'll go back and watch as many as I can. Thanks again!

[JamesDeckert]

Not to ruin your fun, but I reformatted the code using loops and indents (apparently copy/paste looses the indents).
The advantage besides the code being much shorter is that it's much easier to see logic problems like you had.

Function CreateWorkRateTest()


Dim myDB As DAO.Database
Dim rst As DAO.Recordset
Dim bytCodeCount As Byte
Dim i As Variant, j As Variant, k As Variant, l1 As Variant, l2 As Variant, l3 As Variant, l4 As Variant
Dim strSQL As String, strPrompt As String, strTitle As String


Set myDB = CurrentDb()
On Error Resume Next
myDB.Execute "DROP TABLE CodeTable;"
On Error GoTo 0
myDB.Execute "CREATE TABLE CodeTable (CodeID BYTE, Letter1 TEXT(1), Number1 TEXT(1), Shape1 TEXT(1), " & _
"Letter2 TEXT(1), Number2 TEXT(1), Shape2 TEXT(1));"


myDB.Execute "ALTER TABLE CodeTable ADD COLUMN Letter3 TEXT(1);"
myDB.Execute "ALTER TABLE CodeTable ADD COLUMN Number3 TEXT(1);"
myDB.Execute "ALTER TABLE CodeTable ADD COLUMN Shape3 TEXT(1);"
myDB.Execute "ALTER TABLE CodeTable ADD COLUMN Letter4 TEXT(1);"
myDB.Execute "ALTER TABLE CodeTable ADD COLUMN Number4 TEXT(1);"
myDB.Execute "ALTER TABLE CodeTable ADD COLUMN Shape4 TEXT(1);"


strSQL = "SELECT * FROM CodeTable;"


Set rst = myDB.OpenRecordset(strSQL, dbOpenDynaset)


On Error GoTo ErrorCode


strTitle = "Number of Codes"
strPrompt = "Enter the number of Codes to be generated"
bytCodeCount = InputBox(strPrompt, strTitle, 20) 'Prompt for the number of Codes to be generated (20)


For i = 1 To bytCodeCount
rst.AddNew
rst("CodeID") = i

l1 = getrnd()

Do
l2 = getrnd
Loop Until l2 <> l1

Do
l3 = getrnd()
Loop Until l3 <> l2 And l3 <> l1

Do
l4 = getrnd()
Loop Until l4 <> l3 And l4 <> l2 And l4 <> l1

MsgBox "l1 = " & l1 & Chr(13) & _
"l2 = " & l2 & Chr(13) & _
"l3 = " & l3 & Chr(13) & _
"l4 = " & l4
rst("Letter1") = Chr(l1) 'Generates a letter between A and Z
rst("Letter2") = Chr(l2) 'Generates a letter between A and Z
rst("Letter3") = Chr(l3) 'Generates a letter between A and Z
rst("Letter4") = Chr(l4) 'Generates a letter between A and Z
rst.Update
Next i


i = 0
Set rst = Nothing
Set myDB = Nothing


DoCmd.OpenTable "CodeTable"


Exit Function


ErrorCode:


MsgBox Err.Description


End Function
Function getrnd() As Variant
Dim k As Variant
Randomize
k = Int((26 * Rnd) + 1) ' Generate random value between 1 and 26.
getrnd = k + 64
End Function

[Perceptus]

I advise against the use of Variants.

[SiciliandoBlue]

Thanks again James.


My original code had indents, which, as you said, were lost in the copy/paste process. I like the use of the getrnd function to tidy up the code; I never knew how to correctly use subroutines and function, but understand now.


I do have a couple of questions however; for the variants l1, l3 and l4, the getrnd is followed by the parentheses (), but for l2 it is not; the code works as it is, so what is the significance (if any) of the parentheses??


What is the significance of the On Error Resume Next after Set myDB = CurrentDb(), and what does On Error GoTo 0 do? Do you agree with Perceptus to avoid using Variants?


Cheers,


Rod

[orange]

A few points:

In Access/vba all variables must be explicitly DIMmed to be assigned a datatype.

eg Dim x as String, myCount as Long

If you do not do this explicitly, then variables default to Variant. I prefer to explicitly Dim to ensure the variables are typed as I want them.

On Error Resume Next is just going to the next line is an error occurs

On Error GoTo 0 turns off error checking

See these links for Error Handling and Debugging

Good luck.

[JamesDeckert]

Yes I avoid variants. If I know that the l1 variable is to be a double, why not make it a double? It is smaller in memory and will keep Access from treating as a non-number.
It also will prevent you from needing to do additional coding to verify things.
e.g. If i'm getting a value from a user which should be numeric and you define it as a variant you'll need to do
if isnumeric(var) then
else
msgbox "some error
end if
whereas if you define the variable as an int or double, then there is no need to check if it's numeric because Access will error and not put a string value into a number variable.

I added the on error resume next because I was running your code on my system which did not have the table, so I kept getting an error on the DROP TABLE, so I could have commented the line, but just did the resume next which upon an error continues executing with the next line. I intended to remove this from the code when pasting my code back up here. the on error goto 0 turns off the resume next that I set so Access will throw errors again.

[SiciliandoBlue]

Thanks to everyone who has contributed. The code is now working exactly as I intended!