Randomizing a filter

[mshmyob]

I am trying to randomize my filter. I have a table with a record that has a possible value of "Easy", "Medium", or "Hard".

The user makes a choice by a combo box. After it is chosen I activate the Filter property like so

Private Sub cboDifficulty_Click()
Dim strFilterDifficulty As Variant
strFilterDifficulty = Forms!frmGuess.cboDifficulty

Me.Filter = "Word_Difficulty = '" & strFilterDifficulty & "'"
Me.FilterOn = True
Me.txtAnswer = Word_Description
' get the length of the word
Call WordLength(Me.txtAnswer)
End Sub

This filters my recordset based on the combo box value and starts at the 1st occurance. It always appears in the same order obviously. What I would like to do is randomize the order each time.

Any ideas?

[Rabbit]

Hmm... what about a query that creates a new column that just returns a random number from 1 to... let's say 32,767 and then sort by that? Then you can just use that query as the recordsource for the form.

[FishVal]

Hi, there.

The most obvious idea is to add a field to the source table and fill it with random values before applying filter. Like the following.

Public Function GetRandomValue(varDummy As Variant) As Long
    GetRandomValue = (Rnd - 0.5) * 4000000000#
End Function
 

UPDATE [Your table] SET [Your table].lngRand = GetRandomValue([Any table field]);
 

Regards,
Fish

[Rabbit]

Query doesn't seem to work with randomizing numbers. Well, you could always create the column in the table and then use a recordset loop to randomize the numbers.

[Rabbit]

Hi, there.

The most obvious idea is to add a field to the source table and fill it with random values before applying filter. Like the following.

Public Function GetRandomValue(varDummy As Variant) As Long
GetRandomValue = (Rnd - 0.5) * 4000000000#
End Function
 

UPDATE [Your table] SET [Your table].lngRand = GetRandomValue([Any table field]);
 

Regards,
Fish

I tried something similar to this, it just kept returning the same number. I think you have to go with a recordset loop.

[FishVal]

Query doesn't seem to work with randomizing numbers. Well, you could always create the column in the table and then use a recordset loop to randomize the numbers.

Actually one can use Rnd() in a query to get random numbers, but Access frequently refreshes recordset of opened query, so the numbers will continuously change.

[FishVal]

I tried something similar to this, it just kept returning the same number. I think you have to go with a recordset loop.

Pay attention to the special varDummy argument. LOL.

[Rabbit]

Actually one can use Rnd() in a query to get random numbers, but Access frequently refreshes recordset of opened query, so the numbers will continuously change.

Really? I tried to do it with a small recordset, only 4 records.

Instead of an update, I used:

SELECT *, ReturnRnd() As RandomNbr
FROM Table1;
 

I kept getting the same numbers for all the records.
My ReturnRnd() Function was pretty much the same as yours.

[Rabbit]

Pay attention to the special varDummy argument. LOL.

Oh, didn't see that. What's the varDummy do?

[FishVal]

Oh, didn't see that. What's the varDummy do?

Simply enforces the query to run the function for each record.

[Rabbit]

Simply enforce the query to run the function for each record.

I see, so that's why mines didn't work. I didn't realize the query wouldn't run the function for each record unless you supplied an argument. Makes sense.

[mshmyob]

I tried what you said.

I have the Public function like you indicated. Then in the ON Load event for the form I put the rest of your code like so

strSQL = "UPDATE tblWord SET tblWord.Word_Random = " & GetRandomValue(Word_Description) & ";"
DoCmd.RunSQL (strSQL)


(Note the Update statement is all on 1 line)

It updates 21 records but with the same number.

What am I doing wrong. Should I create a loop. According to your post the varDummy variable should run once for each record.

[FishVal]

I tried what you said.

I have the Public function like you indicated. Then in the ON Load event for the form I put the rest of your code like so

strSQL = "UPDATE tblWord SET tblWord.Word_Random = " & GetRandomValue(Word_Description) & ";"
DoCmd.RunSQL (strSQL)


(Note the Update statement is all on 1 line)

It updates 21 records but with the same number.

What am I doing wrong. Should I create a loop. According to your post the varDummy variable should run once for each record.

I guess [Word_Description] is a field in table [tblWord].

strSQL = "UPDATE tblWord SET tblWord.Word_Random = GetRandomValue([Word_Description]);"
DoCmd.RunSQL (strSQL)
 

See the difference?

Regards,
Fish

[mshmyob]

I see the difference. The problem is that when I try it that way I get an "undefined function GetRandomValue in expression" error. That is why I did it the other way.

So maybe my setting up of the public function is wrong.

I just put it at the top of my code. Does it have to be somewhere specific? Is there a special syntax for the public function? I copied it from your post and just placed it at the top of the code outside of all subs.

I guess [Word_Description] is a field in table [tblWord].

strSQL = "UPDATE tblWord SET tblWord.Word_Random = GetRandomValue([Word_Description]);"
DoCmd.RunSQL (strSQL)
 

See the difference?

Regards,
Fish

[FishVal]

Did you add the code of the function to code module (not form or report or any other class module)?

Anyway see attachment.

[mshmyob]

Thanks Fish. Got it now. I put the function in the wrong place.

You are a saviour (lol).

Did you add the code of the function to code module (not form or report or any other class module)?

Anyway see attachment.

[FishVal]

Not a problem.
Good luck.