Go to random records

[Teddy]

SOLVED:Go to random records

I have a form named frmForm1 which is linked to two tables called tblTable1 and tblTable2; tblTable2 is linked to the subform while tblTable1 is linked to the main form. I have a command button on the form, when I click it I would like for it to jump to and/or select one random record from tblTable1 and display it in the main form field in frmForm1 while simultaneously jumping to and/or selecting 3 random records from tblTable2 and displaying them in the subform field in frmForm1. The tables are not linked. Do you know what code would enable me to do this?

 

[McSwifty]

Hi Teddy,

Create a query from your Data and add the line at the top of an empty query

expr1: Rnd(Len([FieldNameGoesHere]))

ensure the properties are set to Top and choose 1.
You do not have to show this on the form so it does not matter if it is visible or not.

then do this again for a new query and set top to 3.

You can put order by ascending or descending, it depends on whether you want them ordered.

then just put all the other fields you want into your query and use those two queries for you forms record source.

By using the RND() - random number with the LEN() - length, you get a different random number each time, if you just use RND by itself it only gives you the same number each time.

HTH.

 

[Mike375]

McSwifty

That is great stuff.

I have always appended to a table with a Rnd field (all records deleted first) then used that to update a field in my table and then sort.

So I have had to use a Delete, Append and then Update queries.