asked on

Find Random Record in Access 2010

I want to click a button on my vocabulary database, so that a random record is shown each time I click.

I could do it if there was an autonumberID, but in this case the unique field is the word itself (Required + Indexed + No Duplicates Allowed)

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eirman

ASKER

So why not add an autonumber?

I will if I have to, but maybe someone will come up with a solution for this situation. It's a very basic database with no relationships.

SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eirman

ASKER

Thanks mbizup (and well researched), I'm sure that will do the job, but I have decided not to use it, as the code is long and may need to be modified for access 2010.

While I was waiting for responses to roll in I took your advice and added an auto number field and filled it for every record (append query into a dataless table + the autonum field).

So I'm now going for a simpler solution. What do you think of this .....
http://www.cosnetics.co.uk/articles/select-random-records-from-access/

EDIT: I'm not sure where to use the code
SELECT TOP 5 * FROM [tableName] ORDER BY rnd(INT(NOW*id)-NOW*id)

EDIT2: got it
Me.RecordSource = "SELECT TOP 3 * FROM VOCAB ORDER BY rnd(INT(NOW*WORDid)-NOW*WORDid);"

Eirman

ASKER

My previous comment says it all.
Thanks mbizup.

mbizup

Glad you got it working.

I thought that the sheer volume of code in the fontstuff article, even though the method works, actually in itself made a very good point of WHY you might want to simply go the autonumber route. ;-)