Returning same series of random values

Question

I have a button on a split form that opens an Update Query, which updates the Random ID field of my table called Items, and then sorts the table by the Random ID. It updates the field with "Rnd([ID])*Rnd(Now())", making what appear to be sufficiently random values.It seemed to be working just fine, until I realized what was wrong.

Every time I open Access, load the database form, and click the button, I get the same series of random values. The first time I click the button, for instance, the item at the top is item X, with the Random ID having been set to 1.53779983520508E-05. If I click it again, item Y is now at the top, with its random ID as 9.06816168821933E-08. A third time, and item Z is at the top, with random ID 1.8881419094896E-08.

I can keep pressing the button all day and get completely new orders and numbers, which suits me just fine. The problem is when I have to re-open the database.

No matter what order the table starts in, even if I revert it to using another field for the sort order completely, when I first click on the Randomize button after opening the database, Item X is always at the top, always with the value 9.06816168821933E-08. On the second click, Item Y with 1.53779983520508E-05. And the third click, Item Z with 1.8881419094896E-08. Each successive click always returns the same set of random ID values as it did the last time I reached that point in the previous session.

I have tried adding to the Randomize button to RunCode and call RandomizeFunc(), which is a function I've written as thus:

Option Compare Database
-----
Public Function RandomizeFunc()
Randomize
End Function

I understood that just calling Randomize resets the random seed. But it doesn't make any difference. The values are always the same in sequence.

Can anyone tell me what the hell I'm doing wrong?

Edit:

This is the Assign Random ID query

Assign Random ID
-----
Field:  Random ID
Table:  Items
Update To:  Rnd([ID])*Rnd(Now())*Rnd([ID]*Now())

(I added some extraneous Rnd's in a vain attempt to get the values to stop repeating...)

This is what the Randomize Button embedded macro looks like

Randomize : On Click
-----
RunCode
    Function Name   RandomizeFunc()
OpenQuery
    QueryName   Assign Random ID
    View        Datasheet
    Data Mode   Read Only
SetOrderBy
    OrderBy     [Random ID]
Control Name

How is the code using `Rnd`? And, where/when is `RandomizeFunc` called? — user2864740, May 03 '14 at 03:40

score 0 · Answer 1 · edited May 23 '17 at 11:50

I do not have a real solution here, but I do have some pointers. The Rnd function resets when called with a negative argument (usefull post on same subject here). Is it possible that your code starts with a negative argument when you do your first run?

Example:

Sub jzz()
Dim i As Long

Rnd (-1)

For i = 1 To 10
    Debug.Print Rnd
Next i

End Sub

which will spit out all the same numbers again and again (same sequence of 10).

If you add randomize, it will make random numbers, but only if randomize is called after the Rnd (-1) statement. If you do it before, it will be undone by the negative value of Rnd. So:

Sub jzz()
Dim i As Long

Rnd (-1)
Randomize 'make sure you call it without arguments

For i = 1 To 10
    Debug.Print Rnd
Next i

End Sub

Will deliver random numbers.

So maybe (but I don't the rest of your code in mind) you can try to discard the [ID] field from your rnd function call. Calling randomize and then rnd (both without arguments) should give you random numbers.

Returning same series of random values

1 Answers1