Randomize

[didacticone]

Hi,

I am trying to create a form, that will randomize data from a table. For example, I have a table called "tbl_master ". It has 3 fields, sport, athlete, speed. I would like the form on a button press to take one random entry from each field. I have managed to do this with one field, but cannot figure out how to do it for all 3. What i have done so far is created a select query and use the following:

SELECT TOP 1 tbl_master.sport
FROM tbl_master
ORDER BY Rnd(Len([sport])) DESC;

That will randomize the sport field, but I am unsure how to make this work to randomize all fields. Thanks guys!

[NeoPa]

Interesting question.

I'm not sure this can be done very effectively using only SQL.

Personally I'd have a Form bound to the table but three Controls that call an internal Function to populate the values. The Form, being bound, would have Recordset and RecordsetClone properties, even though the current record would never be visible as such due to there being no bound Controls to show it.

The three Controls that are there (txtSport, txtAthlete & txtSpeed) would have their ControlSources set to =RandomVal('Spo rt'), =RandomVal('Ath lete') & =RandomVal('Spe ed') respectively and the code for RandomVal() would be something like (Air code.) :

Private Function RandomVal(strField As String) As Variant
    Static lngNumRecs As Long
    Dim lngMove As Long

    With Me.RecordsetClone
        If lngNumRecs = 0 Then
            Call .MoveLast()
            lngNumRecs = .RecordCount
            Call Rnd(Number:=-lngNumRecs * Timer())
        End If
        Call .MoveFirst()
        lngMove = CLng(Format(Rnd() * lngNumRecs + 0.5, "0")) - 1
        If lngMove > 0 Then Call .Move(Rows:=lngMove)
        RandomVal = .Fields(strField).Value
    End With
End Function

* Corrected poor usage of Rnd().

[Rabbit]

Nothing's preventing you from selecting additional fields

[cactusdata]

You will have to use subqueries, for example:

SELECT TOP 1 
    ID,
    
    (SELECT TOP 1 Sport1
    FROM tbl_master
    ORDER BY Rnd(-Timer()*[ID])) As Value1,

    (SELECT TOP 1 Sport2
    FROM tbl_master
    ORDER BY Rnd(-Timer()*[ID])) As Value2,

    (SELECT TOP 1 Sport3
    FROM tbl_master
    ORDER BY Rnd(-Timer()*[ID])) As Value3

FROM tbl_master

Note please, the use of Timer() and an ID with Rnd. If not, you'll get the same result whenever you close Access, relaunch, and run the query.

[didacticone]

Neo and Cactus, I tried both of your suggestions and they both work. I'm going to test them out some more and see the pros and cons of each, but thanks so much for the help!

[NeoPa]

There you have a version that will work for you in SQL too. Excellent.

My preference is still to use the function approach as that simply moves around within a single Recordset object and doesn't require the table to be processed through so much. There again, your original request used SQL as an example so may well be more what you're after. You have both now.

CactusData's post also reminded me to be more careful of my use of the Rnd() function. It does indeed require a negative number in order to seed it - although what can work pretty reliably is making a conscious choice NOT to seed it at all and simply continuing from whenever it was last used - on the basis that, in most cases at least, that is pretty random in itself.

However, my code has been amended to reflect seeding it pseudo-randomly the first time it's called in a session.

Rabbit:
Nothing's preventing you from selecting additional fields

Although I'm not absolutely clear on what you mean and what you're replying to I assume you're suggesting SELECTing multiple Fields from the same, randomly selected, Record.

I suspect that's not what the OP's after. My understanding of the request is that they're specifically looking for each Field value selected separately and randomly from within the distinct sets of data for that particular Field.

[didacticone]

Thanks for the follow-up neo, and your interpretation was 100% correct, I want each field value selected separately and randomly, and you stated. I have a follow up if you don't mind. With either method, would there be a way to just "requery" one of the fields and not the entire query. I am currently working using cactus' method and if I create a button with the me.requery it will randomize all of the fields. I am unsure as to how, if possible to have a button next to each field and randomize only that field from within the form?

[cactusdata]

You could save each of the subqueries above as a separate query, say, Q1, Q2, and Q3.
Then use DLookup in the ControlSource of the three textboxes, like:

=DLookup("[Sport1]","Q1")
=DLookup("[Sport2]","Q2")
=DLookup("[Sport3]","Q3")

Each textbox can now be requeried at will.

[didacticone]

That's what I was actually going to do lol, but then I thought, with more data that I add I would have a lot of queries and was just curious if there was a more condensed way to do it that anyone knew of. I'm by no means an advanced user and usually my solutions are work arounds until I learn a better way. Thank you for the feedback!

[Rabbit]

Ah, my bad, I misunderstood the question

[didacticone]

Also, when I add a command button, and try to requery the text box, it is requiring all of the text boxes? I have the controlsource set as you outlined and the command button has:

me.sport1.requery

[NeoPa]

You may want to rename the TextBox Controls such that they use different names from the associated Fields. That may help. It would ensure the compiler knows it's the Control you want to Requery rather than the Field.

As I said earlier, my personal preference would be to use the associated code module with the function but I can see you're more comfortable with using queries / SQL.

You'll notice that the suggestion in post #8 is similar to my approach in as much as it sets the ControlSource of each of the Controls to a Function reference. Once you take that step of course, your horizons are extended immensely. You can do almost anything you want using VBA. You can optimise the logic and be less tied to just what's available via SQL or queries directly.

[didacticone]

I tried renaming and it is still doing the same thing, I don't get it. And you are correct I am definitely more comfortable with this route, but my plan was to get it to function and then try it the way you recommended as to learn it a little better. Part of my anticipated issue is, won't I run into the same requery issue doing it your way as well?

[NeoPa]

DidacticOne:
Part of my anticipated issue is, won't I run into the same requery issue doing it your way as well?

I would certainly expect so - yes. Essentially both end up calling a function so what's done in the function is almost irrelevant.

Frankly though, the behaviour you describe is unexpected and doesn't seem to fit with the idea of Requerying an individual TextBox Control - and that certainly is supported as a separate item from Requerying a Form.

In the cirumstances (of not really understanding what is behaving in a way that's unexpected.) it may be worth trying and seeing what you get. After all - what's Me.Sport1 when it's at home? It's not in your original question so harder to guess exactly what you're doing. Just to be very clear - you should be running the Requery() Method of the TextBox Control - not of the Form it's on.

[didacticone]

Hey Neo, so I created the form using your method, and it is still requerying the entire form. I don't understand what you mean when you ask "what is Me.Sport1 when it's at home". Also, maybe I am coding it incorrectly, I am creating a command button, and in the on click event I am putting the code above...

me.sport1.requery

or the different names I have tried.