I need help tying these two functio

[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 records and the second is going to allow me to input how many records are needed for audit based off a sample size chart. Kind of like the Top Value field under query properties. So what I need is how many random records 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

 

[aaahhhrrggghhhh]

Hi.

Firstly, your SetTmpRandNbr() function should be a sub procedure, unless :

a. You want to return a value from the function
b. You wish to run it from a macro

OK, so what is it that you are aiming for/trying to do? You have 1 function (procedure) that simply sets a column/field in the "Random" table to random numbers. The other Sub just continually asks for a percentage value.

Mr Big


Dont be small. be BIG

 

[2176]

Mr. Big,

I do intend on running it from a macro. This database is to randomly pick X number of records to be audited. Based on what kind of audit they are going to do that's how many records they are going to check. The top module I have works perfectly in randomly picking out records to audit, but unless I set Top Value to a certain number it returns them all. I would have no problem setting the number in Top Values in the query properties, but the other users of this program do not work in access and wouldn't understand what to do. So I needed that second module to take the number of records that needs to be audited. Kind of like setting the Top Value without having to actually go into the query and set it that way. I hope this is a little more understandable to what I am needing.
Toya

 

[aaahhhrrggghhhh]

Hi Toya.

I think I would just scrap the code that you have and use mine instead, but copy to a notepad document first, just in case. Mine may pick the same record twice, it can be altered so that it doesn't. If you want that code for that, let me know. Also, why are you wanting to run it from a macro?

OK, what I did was to create a form with a button. When the user clicks the button, the code asks for the amount of records (as in your code). It then goes into a loop that picks that amount of records and on each record, it prints the customer name. I am having to use examples here because I am not completely aware of where your code fits into the system that you are developing. Anyway, make sure that the :

tmptablename = "CalledEntries"

line reads the name of the table that you are actually going to sample data from. So, if the table is "Customers" then should read :

tmptablename = "Customers"

OK, and also make sure that that table has an autonumber field in it. For the purpose of this exercise I shall call it : CalledEntriesID. Change the tmpfldkeyname entry to read the name of your key (Autonumber) field, for example :

tmpfldkeyname = "CustomerID"

OK, so heres the code :

Private Sub btnPicknos_Click()

'Before using this proc, make sure that the table used has an autonumber field. Here I have used CalledEntriesID as a typical example, change the value of tmpfldkeyname to read the name of that key field.

'ask for the number of random records to pick
Dim tmptablename As String, totrecstblnm As Long
tmptablename = "CalledEntries"
tmpfldkeyname = "CalledEntriesID"

totrecstblnm = CLng(DBEngine.Workspaces(0).Databases(0).OpenRecordset("Select count(*) from " & tmptablename).Fields(0))

Dim tmpNoRecsToPick As Long
tmpNoRecsToPick = InputBox("Please enter the number of records to pick &quot

'if the user selects outside the total number of records, then exit
If tmpNoRecsToPick < 1 Or tmpNoRecsToPick > totrecstblnm Then

MsgBox (&quot;Exiting&quot
Exit Sub

End If

Debug.Print &quot;---------------------&quot;

'now pick them and display their record numbers
For i = 1 To tmpNoRecsToPick

Randomize
randnotopick = Int((totrecstblnm * Rnd) + 1)
Do While DBEngine.Workspaces(0).Databases(0).OpenRecordset(&quot;Select count(*) from &quot; & _
tmptablename & &quot; where CalledEntriesID = &quot; & randnotopick).Fields(0) < 1

're-seed
Randomize
'pick a random number for the record number
randnotopick = Int((totrecstblnm * Rnd) + 1)
DoEvents
Loop

Debug.Print &quot;Random Number = &quot; & randnotopick

tmpcustname = CLng(DBEngine.Workspaces(0).Databases(0).OpenRecordset(&quot;select CustomerName from &quot; & tmptablename & &quot; where &quot; & tmpfldkeyname & &quot; = &quot; & randnotopick).Fields(0))

Debug.Print &quot;Sample #&quot; & i & &quot; Customer Name = &quot; & tmpcustname

DoEvents

Next i


End Sub
Dont be small. be BIG