Setting random number range based on query, not table

  • Thread starter Thread starter RBear3
  • Start date Start date
R

RBear3

I have a "quiz" form that randomly pulls up questions from my table. It
works fine, and I don't mind that it repeats questions. This allows me to
sit and review for as long as I want.

The problem is, that I want to use criteria in a query to limit the
available questions. If I base this form on a query and limit the number of
questions, the code I am using is still setting the maximum number as the
number of records in my table.

In the past, I had a similar database (which I have lost) that would open
the form, set the selector to the last record available to the form, then
record that number as the max in my random setting. I think this portion of
the code was done in the event that fires when the form opens.

Can anyone help me change the following code so that I can do what I outline
above? I know I will have to move the "Dim NumOfRecords" line to my form's
open event, and I will have to add a line to jump to the last record. I'll
also have to move the "NumOfRecs=..." statement to my form's open event.



Private Sub Next_Question_Click()
Dim MyValue As Long
Dim NumOfRecs As Long

Answer.Visible = False

NumOfRecs = DCount("*", "Questions - Art")
MyValue = Int((NumOfRecs * Rnd) + 1)
DoCmd.GoToRecord , , acGoTo, MyValue
End Sub
 
Hi

Use something like the following in the form's open event...

With Me.RecordsetClone
If .BOF And .EOF Then
MsgBox "No Records"
Else
.MoveLast
MsgBox .RecordCount 'obviously set your variable here!
End If
End With

hth

Regards

Andy Hull
 
I tried the following. But, it goes to one record (I think the first one)
and then just keeps pulling up that same question. It is not jumping to
random records. Any ideas?

Private Sub Form_Open(Cancel As Integer)
Dim NumOfRecs As Long
With Me.RecordsetClone
.MoveLast
NumOfRecs = .RecordCount
End With
End Sub

Private Sub Next_Question_Click()
Dim MyValue As Long
Answer.Visible = False
MyValue = Int((NumOfRecs * Rnd) + 1)
DoCmd.GoToRecord , , acGoTo, MyValue
End Sub
 
FYI...

I put back in the message box, just to make sure I have more than one record
in my form. I do have 30 records in the recordset.
 
Okay, I added a msgbox that pops up "NumOfRecs" inside the OnClick section.
It is always blank.

Why is my "NumOfRecs" 30 when I look at it in the "form open" event, but it
is null in my "on click" event.

Why is the value not getting passed to my "on click" section?

Do I need to make "NumOfRecs" public so that it is passed to other parts of
the code? If so, How do I do that?
 
OKAY, I got it. I had to make that variable public. Here is my code (in
case anyone else looks at this thread)...



Option Compare Database

Public NumOfRecs As Long

Private Sub Form_Current()
Show_Answer.SetFocus
End Sub

Private Sub Form_Open(Cancel As Integer)
Answer.Visible = False

With Me.RecordsetClone
.MoveLast
NumOfRecs = .RecordCount
End With

End Sub

Private Sub Next_Question_Click()
Dim MyValue As Long

Answer.Visible = False

MyValue = Int((NumOfRecs * Rnd) + 1)
DoCmd.GoToRecord , , acGoTo, MyValue
End Sub

Private Sub Show_answer_Click()
Answer.Visible = True
Next_Question.SetFocus
End Sub

Private Sub Text15_Click()
Me.Pictures.SetFocus
End Sub
 
Hi

Glad that helped and that you got it working.

Just for your info, my original reply had the following as part of it...
You ought to check that BOF & EOF (Beginning and End Of File) aren't both
True because if they are this means there are no records and when you try to
do the .MoveLast you will get an error. If you know there is always going to
be at least one record then no problem.

Regards

Andy Hull
 
Back
Top