Random to appear on a report

[Sangeetha]

Hi,

I am newbie. I want a report as a output which will generate a random
numbers and the user to will input the number of records to be selected and
the range 9top value and the bottom value).

i copied the below module and entered in a new database, which does not have
any forms, tables or reports. it perfectly works. But, i want the same to be
displayed on a report. Also, the user should supply variables for hi value ,
liw value and the number of records to eb selected. pleas help. Urgent.

Thanks.

Function testGetRandom()
' test getRandomNumbers function

Const lo = 0 ' Low boundary of population
Const hi = 500 ' High boundary of population
Const itms = 30 ' Number of items to return

Dim X As Variant
X = getRandomNumbers(lo, hi, itms)

Dim i As Integer
For i = lo To hi
If X(i) = True Then Debug.Print i
Next i

End Function

Function getRandomNumbers(lo As Integer, hi As Integer, toSelect As Integer)

' Purpose:
' Return an indicated number of unique random numbers from
' a defined population.
' In:
' lo The bottom number in the population
' hi The top number in the population
' e.g: lo = 73, hi = 250
' toSelect The number of items between lo & hi to return.
' Out:
' Variant array of booleans
' If the item is selected item(i) = True
' If the item isn't selected item(i) = False
' Created:
' mgf 25may99

ReDim items(lo To hi) As Variant
Dim selected As Integer

' Seed the randomizer
Randomize

' Generate the array of unique, random items
Do While selected < toSelect
Dim rec As Integer
' Get a number between lo and the hi boundaries
' *From the VBA Help file on Rnd()*
rec = Int((hi - lo + 1) * Rnd + lo)
' If the item hasn't been marked, mark it.
If items(rec) = False Then
items(rec) = True
' Keep track of the number of items selected.
selected = selected + 1
End If
Loop

getRandomNumbers = items

End Function

 

[Allen Browne]

This (untested aircode) example assumes:
- a table named Table1, with numeric primary key named ID,
- a report named Report1, with a source query named Query1.

Private Sub cmdGo_Click()
Dim strSql As String
Randomize
If IsNull(Me.txtHowMany) OR IsNull(Me.txtLow) OR IsNull(Me.txtHigh) Then
MsgBox "All 3 numbers required"
Else
strSql = "SELECT TOP " & Me.txtHowMany & " Table1.* " & _
"FROM Table1 " & _
"WHERE ID Between " & Me.txtLow & " AND " & Me.txtHigh & _
" ORDER BY Rnd(Table1.ID), Table1.ID;"
CurrentDb.QueryDefs("Query1").SQL = strSql
DoCmd.OpenReport "Report1", acViewPreview
End If
End If

Notes:
=====
1. It uses Rnd() to sort the records randomly. JET doesn't bother calling
the function repeatedly unless it has an argument that changes, so you need
to pass the primary key value as the function argument.

2. You cannot use a parameter for the TOP number, so we generate the SQL
statement dynamically, and assign to the SQL property of the QueryDef that
feeds the report.

3. I'm not sure if the high and low limiting values are meaningful. They may
not be if the ID is an autonumber.

 

[Sangeetha]

Hi,

Thanks for your response.But, where should I write this code. It looks like
a command button code.

Thanks.

 

[Allen Browne]

1. Create a form.

2. Add a command button to the form.
(Cancel the wizard if it starts up.)

3. Set the Name of the button to (say) cmdGo.
(That's on the Other tab of the Properties box.)

4. Set the On Click property (Event tab) to:
[Event Procedure]

5. Click the Build button (...) beside that property.
Access opens the code window.
Set up the code there.

 

[Sangeetha]

Wow ! That is wonderful.. I did the same before. But, it did not work. I
found the error or the missing things.

Thanks for your help. I am just descrbing below, as it might be useful for
other readers.

After opening a form, we need to create three text box and name it as
txthowmany, txtlow and txthigh and supply information.

Thanks!!!

1. Create a form.

2. Add a command button to the form.
(Cancel the wizard if it starts up.)

3. Set the Name of the button to (say) cmdGo.
(That's on the Other tab of the Properties box.)

4. Set the On Click property (Event tab) to:
[Event Procedure]

5. Click the Build button (...) beside that property.
Access opens the code window.
Set up the code there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

Thanks for your response.But, where should I write this code. It looks
like
a command button code.

Thanks.