Tying to Functions together!

[2176]

I need help tying these two functions together, I thought I had tried everything, but I guess not since it still doesn't work. The first function actually chooses the random number and the second is going to let me choose how many numbers. So what I need is how many random numbers to choose. All help would be appreciated.


Public Function SetTmpRandNbr()
Dim rs As Recordset
Dim SQLcmd As String
Dim db As Database

Set db = CurrentDb()

SQLcmd = "SELECT * FROM Random ORDER BY TOID"

Set rs = db.OpenRecordset(SQLcmd, dbOpenDynaset)

With rs
Do Until .EOF
.Edit
!TmpRandNbr = Rnd()
.Update
.MoveNext
Loop
.Close
End With
Set rs = Nothing
End Function
-----------------

Sub Yadda()
Dim varResponse As Variant

varResponse = InputBox("Please enter the % of records " _
& "you would like to view.", "Percent to View&quot

If Nz(varResponse) = "" Then
'Do nothing.
ElseIf IsNumeric(varResponse) Then
'Run your code here, using varResponse in place of
'the random number you were generating.
Else
Call MsgBox("That's not a number.&quot
Call Yadda
End If
End Sub

 

[TrojanRabbit]

If I understand correctly, you want to open a percentage of records from a table and assign random numbers for them. Since the first function assigns to all records, it wouldn't be useful for this. You could actually just put the code in Yadda, but, if you want SetTmpRndNbr to be able to assign to a percentage of the records, you would need to give it a parameter. Use the parameter to figure out how many records to edit.
rs.MoveLast
NumberOfRecords = Percentage * rs.RecordCount '(assuming 0<=Percentage<=1)

Then, rather than using a Do loop, use a For loop.
With rs
.MoveFirst
For i = 1 to NumberOfRecords
.Edit
!TmpRandNbr = Rnd()
.Update
.MoveNext
Next i
.Close
End With

 

[2176]

Actually I want a X # of random records to open..We have audits on books and they are random book audits (those are the records I am talking about). Well there are a varying number of books in each file and monthly we have to audit certain files, we don't want to audit all the books in the file just a set number of books. And it doesn't have to be a percentage. A prompt should ask how many books to audit. I input the number and randomly that number is returned. Does that explain a little better what I'm trying to do??

Toya

 

[TrojanRabbit]

Ah, I see. Select a number of random records. That may be a little more complicated. A less practical way might be to set a recordset to the file/table, then randomly go to a record (rs.Move Rnd(),1) and copy it into another recordset. This would allow a record to be repeated, however, unless you use FindFirst in the other recordset. Again, an impractical method. Just something to mention while thinking of a better one.

 

[SBendBuckeye]

I believe if you use the Top N feature without specifying an Order By clause it more or less returns a random order. Would this be sufficient for your needs? If so, you would just need to replace the N with the number of records you want to return.

Please repost if this works for you. I have never actually tried it, just remember reading about it somewhere along the line.

If that doesn't work, you can reference a function in a query if the function resides in a class module. In that case assign your random number to a field in your query, sort on it and use Top N to return the number of records you need.

Good Luck!

 

[MichaelRed]

The 'Top' predicate will not return anything like a randomized set of records. It simply returns the &quot;top&quot; of the recordset. If no order clause is present, the order defaults to the raw record set. Whils this is not necessarily predictable, it is also not random.

This issue was previously discussed, and randomizing the records may be useful/necessary - however it is not (by itself) usually sufficient, since the randomization would nornally generate a 'sequence' of records which would have some records in the 'top' (or other selection criteria) more than once within a required period and have others not within the selection group at all over the required period.

Unfortunatly, I am unable to locate the related thread, so cannnot reference it.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[oharab]

Have a look at:

http://mvps.org/access/queries/qry0011.htm

Simple when you know how!

B ----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------

 

[2176]

Thanks, guys for your help and suggestions, but the first module actually works quite well. It's the second one that I'm having trouble with. The first module there actually returns random numbers, based off the Top Value selected in the query properties...and that would be fine with me because I could change it as necessary, but I'm not going to be the only one accessing it, and the others have never used Access before. So that's the whole problem, trying to have this database as user friendly as possible.

 

[SBendBuckeye]

If I understand you correctly, you want to return X number of random records generated in SetTmpRandNbr which is based on a value returned by the input box in Yadda. The problem you are having is that you close your recordset once you have assigned the random numbers in SetTmpRandNbr so it is no longer available to you in Yadda.

If there aren't response time issues due to large databases, include your input box in SetTmpRandNbr and then return Top N from your table after sorting it on the random number you generated.

If I misunderstood you, please clarify what you are trying to do.

Michael Red is correct, Top N without a predicate simply returns an unpredictable order. I did not mean to imply that it was actually randomized in some manner. Sorry if I caused any confusion.

Good Luck!