Random Function

[Chris A]

I have created a Sub that randomly chooses a loan record
ID from a table successfully, but I need it to check the
table it is the random Id to make sure the ID is not a
duplicate. If it finds a duplicate I need it to not
increment the K and look for another ID that is not a
duplicate. I also need to somehow create a message box
when the random function does not find 5 ID's at random
that says it only found 4, or 3 due to there were not
enough records to create 5 at random.

Below is the code

-Chris


Sub RandomList4()

Dim D As Database, R As Recordset, T As Recordset
Dim Top, Bottom, K
Dim strRecordID As String
Set D = CurrentDb
Set R = D.OpenRecordset("Select * from [tblMake] Where
[Application_User_Name]='" & strcboValue & "';",
dbOpenSnapshot)
Set T = D.OpenRecordset("tblLoans")
DoCmd.SetWarnings False
DoCmd.OpenQuery ("qryDeleteTblLoans")
DoCmd.SetWarnings True

R.MoveLast: R.MoveFirst
Top = 0
Bottom = R.RecordCount - 1

For K = 1 To 5

R.MoveFirst
R.Move Int((Top - Bottom + 1) * Rnd + Bottom)
Randomize
strRecordID = R![Loan_Record_ID]
--> If strRecordID = T![Loan_Record_ID] Then
--> K -1
--> Next
--> Else
T.AddNew
T![Loan_Record_ID] = strRecordID
T.Update
Next
End Sub

 

[Gottfried Lesigang]

Hi Chris!

I am not sure whether I got your problem right, but as far as I understand
you could better use a Collection.

First of all: Build a Class-Modul with ID and other values (Table-Fields) as
properties.
1) Read your table randomly
2) write the content into the properties of a newly created instance
3) use the ID as "Key" with the "Add"-method of the collection.
4) With an "On Error Resume Next" you can proof immediately after the "Add"
wheter it was succesful or not.
5) In case of an error you must do - just nothing. Let the loop continue...
6) After a given time you exit the loop.
7) Reading the "Count"-Property will show you how many entries were found

An even better way would be to read your Table into an array and "shuffle"
it. (If you want I can post an exampel how to shuffle an array)
Afterwards you read e.g. the first 5 entries. Thus you would avoid a
"time-out-loop" ...

HTH
Gottfried

 

[Guest]

Sure, I would like to see an example of how to shuffle an
array.