select random record

[rwahdan1978]

Hi



I have a select statement to get a random record it is working fine the second time but the first time it runs it is always displaying the first record (not random) why is that?

Set rs = CurrentDb.OpenRecordset("SELECT * FROM starter where asked <> True ORDER BY rnd(StarterID)")

[orange]

Please see this article on the RANDOMIZE function.

[rwahdan1978]

my code is ok and working the second time but when starting the app i am always getting the same record not a different one. It is choosing the 7th record always not sure why and how?

[orange]

As per the link I provided

Warning: If you don't call the Randomize function before calling the Rnd function, the Rnd function may return the same random number value each time. And therefore, you may not get a truly random number.

Try adjusting your code to

....
Randomize
Set rs = CurrentDb.OpenRecordset("SELECT * FROM starter where asked <> True ORDER BY rnd(StarterID)")
...

[rwahdan1978]

Still same issue

[orange]

Here's some code to review.

'---------------------------------------------------------------------------------------
' Procedure : randomNumber
' Author    : Jack
' Created   : 11/18/2010
' Purpose   : To Generate Random numbers between and including a range of numbers.
'Lo and Hi are the lowest and highest random numbers you wish to generate.
'
'The Randomize keyword is critical to getting different results for each Access session.
'=======================================================================================
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Function randomNumber(lo As Long, hi As Long) As Long
10       On Error GoTo randomNumber_Error

20    Randomize
30    randomNumber = Int((hi - lo + 1) * Rnd + lo)

40       On Error GoTo 0
randomNumber_Exit:
50       Exit Function

randomNumber_Error:

60        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure randomNumber of Module AccessMonster"
70        GoTo randomNumber_Exit
           
End Function

Here s a test of the function

'---------------------------------------------------------------------------------------
' Procedure : mytestOfRandomNumber
' Author    : Jack
' Date      : 11/18/2010
' Purpose   : Create 10 random numbers between a and z
'---------------------------------------------------------------------------------------
'
Sub mytestOfRandomNumber()
          Dim a As Long
          Dim z As Long
          Dim i As Integer
10        a = 1
20        z = 26
30        For i = 1 To 15
40            Debug.Print i & "   " & randomNumber(a, z)
50        Next i

60       On Error GoTo 0
70       Exit Sub

mytestOfRandomNumber_Error:

80        MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure mytestOfRandomNumber of Module AccessMonster"
End Sub

Sample results based on 3 executions of the function:

1 6
2 16
3 26
4 10
5 7
6 1
7 17
8 22
9 22
10 21
11 24
12 16
13 13
14 16
15 5

1 2
2 7
3 4
4 13
5 12
6 23
7 8
8 20
9 16
10 15
11 1
12 10
13 17
14 2
15 22

1 2
2 12
3 1
4 3
5 10
6 8
7 26
8 6
9 19
10 19
11 7
12 11
13 17
14 5
15 15