Requery/Randomize not working (1 Viewer)

[PeterWieland]

I want to return all the records in a table in a random order. I have a query with Expr1:Rnd([RecordID]) sorted ascending as one of the fields. I have a form with this query as the data source.

This works fine, each time I open the form, I get a different record order.

I now want to use Randomize to return a different order each time I run the database.

Using advice from other threads about randomization, I have put the Randomize in the OnLoad event of the form, immediately followed by Me.Requery, but I still get the same first record each time I open the database.

I put Rnd() in a MsgBox after Randomize to see if the randomization is working, and I get a different number every time, it just seems that the Requery is being ignored.

Any ideas?

 

[gemma-the-husky]

randomize needs a parameter to seed it. if you dont put a parameter in presumably the seed is zero

try randomize(now) which will take the date/time setting as the seed.

 

[PeterWieland]

I have tried it with Now() as a seed, but it is the requery that isn't working, the random number generated works just fine, as shown in the test message box.

 

[gemma-the-husky]

a) do you have any other sort criteria in your query
b) are the random numbers being calculated differently each time in the query

the help for rnd([seed]) sxeems to indicate that including the recordid is reseeding the query each time. I will try this and see what happens.

 

[gemma-the-husky]

i just used the raw query, rather than use a form, so its not quite the same as you. i called a function which to return the rnd value, and set a randomize each time in the function. I seemed to get a different sequence each time.

 

[PeterWieland]

Hi Gemma,

Is that a different record each time you run the query, or each time you run the database. I am getting a different first record on each run of the query, but if I restart the database, the first record of the first run is the same, the first of the second is the same etc.

To illustrate:-
Open database, open the form, get records say 34,19,26,45.......
Close form, reopen and get records 67,24,2,11,42......

Close the database completely, reload and then get exactly the same sequences, which is what the randomize/requery is supposed to stop.

I have the following in the OnLoad event of my form:-

Randomize (Now())

MsgBox (Now() & " , " & Rnd())

Me.Requery

The Rnd function is returning the same number on each new load of the database, even though the Now function is returning a different value!!!

 

[gemma-the-husky]

each time i ran the query - i didn't go as far as designing a form to see what was happening.

As I said, putting rnd(id) in the query design may be causing this.

in the query def try using expr1: myrandom(id)

then create a module with

function myrandom (recid as long) as double


end function

 

[gemma-the-husky]

sorry hit tab and posted - i'm not sure how to edit posts

function myrandom (recid as long) as double
myrandom = rnd
end function

-----note that the recid parameter is ignored, but is required or else your query will use the same random value for each row

now you are not reseeding the generator, so hopefully if you call randomize(now) when opening your form, you WILL get different sequences each time.

Let me know

 

[PeterWieland]

No, makes no difference.

I have attached a cut-down DB to show the problem. Only has 1 table, 1 query, 1 form and 1 module.

If you load the DB and open the form you should get records 7,4,5,2,3,1......

Close the form and open it again, you should get 3,9,10,6,5,1.......

Completely quit Access, load the DB again, and you will get the same 2 sequences.......

Very odd behaviour

 

[gemma-the-husky]

Right - interesting.

A few notes

I've attached a working dbs now, after a bit of playing, cause I was getting the same problem as you. I changed your form to a continuous form - actually a new one, cause i couldnt get the header to display on yours.

Firstly the randomize DOESNT need a seed - it uses the timer automatically, see Access help

When you add your sort parameter to the form (or look at your query), the data isnt actually sorted in accordance with the random number, which I thought might be the case.

What i've done is used an update query to generate and save the random numbers into your table, then based the form on a query which uses the saved value as a sort. This DEFINITELY now generates new random values each time.

Finally I put a button on the form to rerun the update query if you need.

 

[PeterWieland]

Thanks for that, works fine. I just need to try it on my live DB, with over 10,000 records to see if speed is an issue.

It seems that Randomize does not do quite what the documentation would seem to suggest! Ah well, thats Micro$oft for you!