Generate random records without repetition

[jelopy15]

Hi to all,
can you help me with my codes, i want my program not to duplicate any of my records.
what happen is that my records are being generated in random, but it repeatedly shows answered records.
i have public connection.

Private Sub Command1_Click()

Set rs = Nothing
RSconn "select  * from tblexam where category = '" & "123" & "'"
        With rs
        Frame1.Enabled = True
        
        
        a = Int(Rnd * Text1.Text)
     
        RSconn "select  * from tblexam where category = '" & "123" & "' and number = '" & a & "'"
        If rs.RecordCount <> 0 Then

     Me.txtquestion.Text = .Fields!Question
     Me.txtch1.Text = .Fields!choice1
     Me.txtch2.Text = .Fields!choice2
     Me.txtch3.Text = .Fields!choice3
     Me.txtch4.Text = .Fields!choice4
     Me.lblcorrect.Caption = .Fields!correct
     
        End If
End With
End Sub

[dglienna]

First couple of lines (once in the program) add RANDOMIZE statement to seed random number generator.

If you don't want a duplicate, then you have to keep track of what was called before.

Explain the object of your query...

[DataMiser]

Yes you need to add the randomize statement else your random numbers will always occur in the same order. Also you need to place this randomize statement out side your click event, perhaps in form load as you only want it to execute 1 time.

As for no dupes probably the easiest way would be to add a boolean field to the db. Before you begin you could issue an update statement to set the field to false on all records, then as each record is selected at random you could set the field to true on that record. In your where clause add a test to see if the field is false and if not then select another record.

Or you could have a list of numbers selected it would be empty to start with and after each random pick you could check to see if the number is in the list if not then add the number to the list and select your record. If already there then generate another random number and test again, repeat as needed until the number is not there. This would be the better method as it does not issue queries to the database until it sees the record has not been pulled yet.