Solved: Select Random Record

[JustJerry]

I have something I'd like to do in a program just for Simple Amusement for the user. After the user types in there name on the first form, they are then directed to a new form. On this form (called frmStart), I want an unbound Text box to display something like "Welcome X", where X would be some random name selected from a table that contains a list of Funny names. Not a huge table...but say it contains a list of 20 different names.

The table could be called tblJoke.
This table would have a Primary Index (autonumbered) called JokeID. Then a Text Field called JokeName.

Just don't have a clue how to randomly select a record from a table, since I've never ever done it before.

Thank you,

Jerry

[asingh]

Here you go...

Sorry am not creative..have just put the names as Joke1,Joke2,Joke3...in the table tbl_Jokes...they should come out in a randomized manner.


regards,

asingh

[Cosmos75]

Assuming you have a table as follows

Table: tblJokes
Fields
1) PK (Automnumber) - Primary Key
2) strJoke (String)

You can use the following SQL to select 1 random record. You could either use it as a querydef or in constructing an SQL statement to create a recordset in VBA.

SELECT TOP 1 tblJokes.PK, tblJokes.StrText
FROM tblJokes
ORDER BY Rnd([PK]);

[JustJerry]

Thank's to both of you for replying. I got the code in the program now. Appreciate you taking the time to answer my question very much.

Jerry

[JustJerry]

Question??

Not sure if I have to make a new post or not since I marked this solved. I tried using both examples. I noticed that when I started my program, I would get the same result each time, which happened to be the 7th record in my table. It didnt' matter which code I used, I would get the same result. Is there a reason for this?

[Cosmos75]

What field are you passing to Rnd()? I think that you must use a unique number for each record. If you pass the same number you use the same seed for Rnd() for each record and Rnd() will return the same number. Rnd() is a psuedo-random number.

Look up Randomize in the help file and it may help you understand. You could use a function to call that uses Randomize to ensure that each call returns a different number.

EDIT: Try running something like this to see what I mean

SELECT tblJokes.PK, tblJokes.StrText, Rnd() AS RandomNumber_1, Rnd([PK]) AS RandomNumber_2
FROM tblJokes;Be sure that PK is different for every field. You should see that Rnd() returns the same number for each record.

[Cosmos75]

I am not 100% sure but I think that since queries are optimized, a function gets called only as many times as is neded. So if you only use Rnd() in a query, it only gets called once. If you pass different numbers to Rnd() but they are not all unique, then Rnd() is called for each non unique number, but for those records with the same number passed to Rnd(), you get the same number.
Here's a simple function that returns a 'Random' number

Option Compare Database
Option Explicit
Public Function dblRnd(Optional varVariable As Variant) As Double
Randomize Now()
DoEvents
dblRnd = Rnd()

Debug.Print "dblRnd(" & varVariable & ") = " & dblRnd
End Function
Now, using the following query to call the function

SELECT PK, StrText, dblRnd([StrText]) AS dblRnd_Var, dblRnd("Same Text") AS dblRnd
FROM tblJokes;
Since we have a Debug.Print statement we can tell how many times dblRnd() as called. After running the query for tblJokes (which has 15 records), I have the following in my debug window.

dblRnd(Same Text) = 0.687908351421356
dblRnd(Joke 1) = 0.605048775672913
dblRnd(Joke 2) = 0.384785950183868
dblRnd(Joke 3) = 0.779995203018188
dblRnd(Joke 4) = 0.594367444515228
dblRnd(Joke 5) = 0.416829943656921
dblRnd(Joke 6) = 0.68386322259903
dblRnd(Joke 7) = 0.882763385772705
dblRnd(Joke 8) = 0.551642119884491
dblRnd(Joke 9) = 0.545005917549133
dblRnd(Joke 10) = 0.299335300922394
dblRnd(Joke 11) = 3.63471508026123E-02
dblRnd(Joke 12) = 0.559732377529144
dblRnd(Joke 13) = 0.989576697349548
dblRnd(Joke 15) = 0.231202185153961
dblRnd(Joke 1) = 0.506325721740723
dblRnd(Joke 2) = 0.149796664714813
dblRnd(Joke 3) = 0.484963059425354
dblRnd(Joke 4) = 0.213884651660919
dblRnd(Joke 5) = 0.292699098587036
dblRnd(Joke 6) = 0.790676534175873
dblRnd(Joke 7) = 0.562323451042175
dblRnd(Joke 8) = 0.51296192407608
dblRnd(Joke 9) = 0.395467281341553
dblRnd(Joke 10) = 0.747951209545136
dblRnd(Joke 11) = 0.690499424934387
dblRnd(Joke 12) = 0.128434002399445
dblRnd(Joke 13) = 0.54905104637146
dblRnd(Joke 15) = 2.16206908226013E-02
Since dblRnd() get called for each record, the Randomize statement will reset the seed used to generate a random number.
I think you can cause Rnd() to be called, without the use of a custom function if you passed a calculated value to it. Using Len() should work.
[code]SELECT PK, StrText, dblRnd(Len([StrText])) AS dblRnd_Len
FROM tblJokes;

code]
This only seems to work if you pass a calculated field and not use Rnd() in a calculation.
OK - Rnd(Len([StrText]))
NOT OK - Len([StrText])*Rnd()

Here's a new version of the sample file so you can see it for yourself. Hope this helps!

[asingh]

I used the following to get "randomness" in the program...

[VBA]
Randomize (Now())
RndNmbr = Int((19 - 0) * Rnd + 0)
[/[VBA]

I passed the current system time as the 'seed'...and then used the RndNmbr value to navigate to a random position in the current recordset, and pick the respective joke.

Incidentally..when I start up the program...I dont always get the 7th record....!

[JustJerry]

Sorry for the late reply, just now getting back to this.

asingh, that is what I thought as well, about the current time as the seed. I had the code in exactly as you had it, I swear!!

I am going to try to get back to this this afternoon or tomorrow morning and will report back.

Thanks a bunch~

[JustJerry]

Well, it's been a month now, and I just NOW got to work on my program again. Vacation and all...:whistle:

I actually searched around and this is what I got to work

WHEN the form loads I simply put this:
Private Sub Form_Load()
Randomize
End Sub

Then, I took asingh's code and tweaked it to the following:

Dim stDocName As String
Dim jk As String
Dim i As Integer
Dim RndNmbr As Single
Dim rst As ADODB.Recordset
stDocName = "frmStart"

Set rst = New ADODB.Recordset


rst.Open "SELECT * FROM tblJokeName", CurrentProject.Connection, adOpenStatic
'select the Jokes names

RndNmbr = Int((10 * Rnd + 1))
'randomize it

For i = 0 To RndNmbr
rst.MoveNext
Next i
'get to the random Record

jk = rst(1)
'assign it

rst.Close
Set rst = Nothing

DoCmd.Close
'close the main form
DoCmd.OpenForm stDocName
Form_frmStart.lblWelcome.Value = "WELCOME " & jk
'display on joke_form......!

It works for me now and I don't get the same results each time I open up Access.

Thank you again for your help...trully appeciated