Using a query that creates a random result as the criteria for the a field in another table (1 Viewer)

[Hawkins1989]

Hi All,

I have a test database that I am using some for next looping to enter records to quickly make a lot of records for testing.

The table has only a few fields which is a date, disease (I work in a hospital hence this field) and new diagnosis which is a tick box.
I then have a second table with different diseases, from this I made a query that has a random number field and shows just a single result which is a randomized disease each time it is run.

I want to use this random result in vba with the loop and adding a new row in the recordset, this is where I am falling short I don't can't figure out how to link that query result to make it the value of the field. I tried holding it in a tempvar tried multiple types of DoCmd.OpenQuery and opening the query and typing the SQL but nothing seems to work.

This is what I have done so far, sorry for the pic, I wasn't sure what option to use to copy the code directly like other people do. I tried putting the DiseaseName recordset to set itself just inside the loop so it runs each time to get a different value, I would also like to randomize the new diagnosis tick box and the mod 5 seems to work but I imagine there is a better way to make it truly randomized.

Any suggestions would be very helpful.
Many thanks
Matt

 

[Hawkins1989]

Hi All,

I have sorted it myself, I used a DLookup in the vba and that has worked, see below in case anyone else is wanting to do a similar thing

Thanks
Matt

 

[Gasman]

Please post code as code within code tags </>, not as pictures.
If someone had to take that code to tweak it, they would have to key it all in, which is highly unlikely to happen.

 

[ebs17]

A suggestion:
The strength of SQL is methods of mass data processing = doing a lot at once.
The following query sorts the contents of a table randomly, duplicates this using the CROSS JOIN with the subquery and fixes the result into a new table.

SELECT
   Y.*
INTO
   NewTable
FROM
   YourTable AS Y,
   (
      SELECT TOP 10
         ID
      FROM
         YourTable
   ) AS X
ORDER BY
   Rnd(Y.ID)