go to a random record on a form...

[RBear3]

I have a database where I have entered questions and answers to help me
study. I base the form on a query which prompts me for the "topic" I wish
to study.

When the form opens, the "answer" field is invisible. I press a button and
the answer is displayed. I press the "next" button, and the form moves to
another record and hides the answer.

Here's the issue. I'd like to move to a random record, not the next record
in the query. I have done this before, but can't seem to find that old
database that used the randomize function in a form.

Can someone give me step-by-step directions?

I am pretty sure I will have to generate a record between 1 and X (where X
is the number of records selected by my query). I will then tie code to my
button that says "go to record Y" (where Y is the random number I generated
in the previous sentence).

Thanks for the help!

 

[Bob Hairgrove]

[cross-posting deleted]

I have a database where I have entered questions and answers to help me
study. I base the form on a query which prompts me for the "topic" I wish
to study.

When the form opens, the "answer" field is invisible. I press a button and
the answer is displayed. I press the "next" button, and the form moves to
another record and hides the answer.

Here's the issue. I'd like to move to a random record, not the next record
in the query. I have done this before, but can't seem to find that old
database that used the randomize function in a form.

Can someone give me step-by-step directions?

I am pretty sure I will have to generate a record between 1 and X (whereX
is the number of records selected by my query). I will then tie code tomy
button that says "go to record Y" (where Y is the random number I generated
in the previous sentence).

Thanks for the help!

In addition to the randomness requirement, you also need to eliminate the
possibility of revisiting a question which has already been answered. Have you
thought of this?

The easiest way to do it is to include a calculated column in your selectquery
which takes the primary key value as an argument and generates a pseudo-random
number (i.e. some kind of hash function) which you use to sort the rows of the
query. Base the form's recordsource on this query, not on the underlying table.

You will need to include a criteria expression in the query which excludes
records which already have an answer. Then you can just requery the form in the
VBA code which is run when the answer is submitted ... the focus will always be
on the first row, but it will always be a different row than the last time. Use
"SELECT TOP 1" if you like; that way the user will not be tempted to navigate to
a different row without having answered the question at hand.

 

[Arvin Meyer [MVP]]

This is what I use:

Function GetRandomRecords() As Integer
Static intPicked As Integer
If intPicked = False Then Randomize
intPicked = True
GetRandomRecords = 0
End Function

Then run a query like:

SELECT TOP 1 * FROM MyTable
WHERE GetRandomRecords() = 0
ORDER BY Rnd(IsNull(IDField) * 0 + 1)

You can set the TOP value to any number of records

 

[Guest]

Private Function cmdSelectARec_Click()
Static blnRandom
Dim lngAnyRecord As Long

Set rst = Me.RecordsetClone

If Not blnRandom Then
Randomize
blnRandom = True
End If

lngAnyRecord = Int(( rst.RecordCount * Rnd) + 1)

rst.Move lngAnyRecord

Me.Bookmark = rst.Bookmark

Set rst = Nothing

End Sub

 

[RBear3]

Thank you all. I got it working.