Function to random generate a number

[Dale C Gray]

This function is supposed to randomly generate a number, look to see if it
exits in the recordset, if not, udate the record....if it does find an
existing number, the intention was the loop would start the process over
again until the random number was updated with one that does not previously
exist. I occassionally get duplicates? Have I missed something?

Thanks in advance

Function RandomNum()
Dim db As DATABASE
Dim rst As Recordset
Dim lngcounter As Long
Dim lngRandom As String
Dim upperlimit As Long
Dim lowerlimit As Long
Dim strCriteria As String
Dim mynum as Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("tblPatient", dbOpenDynaset)

Do Until rst.NoMatch
upperlimit = 999999999
lowerlimit = 100000000
Randomize
lngRandom = Int((upperlimit - lowerlimit + 1) * Rnd + lowerlimit)

rst.FindFirst "[mynum] = 'lngRandom'"
If rst.NoMatch Then

mynum = lngRandom

End If


Loop

' Clean up.
rst.Close
Set db = Nothing
End Function

 

[jfp]

assuming that your random number is stored in the table as a number, why
are you enclosing it in single quotes as the criterion for Find ?
-=-=

 

[Dale C Gray]

This is a function behind the update event on a form, the field is a string.

assuming that your random number is stored in the table as a number, why
are you enclosing it in single quotes as the criterion for Find ?
-=-=

This function is supposed to randomly generate a number, look to see if it
exits in the recordset, if not, udate the record....if it does find an
existing number, the intention was the loop would start the process over
again until the random number was updated with one that does not previously
exist. I occassionally get duplicates? Have I missed something?

Thanks in advance

Function RandomNum()
Dim db As DATABASE
Dim rst As Recordset
Dim lngcounter As Long
Dim lngRandom As String
Dim upperlimit As Long
Dim lowerlimit As Long
Dim strCriteria As String
Dim mynum as Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("tblPatient", dbOpenDynaset)

Do Until rst.NoMatch
upperlimit = 999999999
lowerlimit = 100000000
Randomize
lngRandom = Int((upperlimit - lowerlimit + 1) * Rnd + lowerlimit)

rst.FindFirst "[mynum] = 'lngRandom'"
If rst.NoMatch Then

mynum = lngRandom

End If


Loop

' Clean up.
rst.Close
Set db = Nothing
End Function

 

[jfp]

on the surface it looks good but there may be weird things happening re
the recordset. I don't know if this is so, but you should rewrite the
code to make sure:

Do
... FindFirst ...
If (rst.NoMatch) then Exit Do
Loop

mynum = ...

I don't know what happens to the NoMatch property after you do things
like making a new record or editing an existing record. YOu are
trusting it to stay unchanged so that you can (unnecessarily) test it a
second time at the top of your loop. Perhaps this is causing you to
stay in the loop and eventually generate the same value. Depending on
how the recordset refreshes (again, i don't know), you might not find
the previously inserted value.

Just some guesses, but it won't hurt to clean up the logic a bit.
-=-=

This is a function behind the update event on a form, the field is a string.

assuming that your random number is stored in the table as a number, why
are you enclosing it in single quotes as the criterion for Find ?
-=-=

This function is supposed to randomly generate a number, look to see if it
exits in the recordset, if not, udate the record....if it does find an
existing number, the intention was the loop would start the process over
again until the random number was updated with one that does not previously
exist. I occassionally get duplicates? Have I missed something?

Thanks in advance

Function RandomNum()
Dim db As DATABASE
Dim rst As Recordset
Dim lngcounter As Long
Dim lngRandom As String
Dim upperlimit As Long
Dim lowerlimit As Long
Dim strCriteria As String
Dim mynum as Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("tblPatient", dbOpenDynaset)

Do Until rst.NoMatch
upperlimit = 999999999
lowerlimit = 100000000
Randomize
lngRandom = Int((upperlimit - lowerlimit + 1) * Rnd + lowerlimit)

rst.FindFirst "[mynum] = 'lngRandom'"
If rst.NoMatch Then

mynum = lngRandom

End If


Loop

' Clean up.
rst.Close
Set db = Nothing
End Function