How to choose a random record from MS Access after connecting it to a VS2017 project?

Question

I was wondering if there's a way to choose a random record after using the "Connect to Database" tool within Visual Studio 2017. I'm trying to select a random record between 1 and 21. The MS access database has QNumber (Primary Key), Question, Answer1, Answer2, Answer3 and Answer4. QNumber is a Number whereas every other is Text.

I tried using this code from the official Microsoft site.

Dim MyValue As Integer
MyValue = Int((6 * Rnd) + 1)    ' Generate random value between 1 and 6. 

However, I wasn't sure how to apply it to my situation.

Since using the "Connect to Database" tool, I've used this code to insert the data from the MS Access Database to fill in my text boxes:

txtQNumber.DataBindings.Add("Text", QuizDataSet, "Quiz.QNumber")
txtQ.DataBindings.Add("Text", QuizDataSet, "Quiz.Question")
txtAnswer.DataBindings.Add("Text", QuizDataSet, "Quiz.Answer1")
txtAnswer2.DataBindings.Add("Text", QuizDataSet, "Quiz.Answer2")
txtAnswer3.DataBindings.Add("Text", QuizDataSet, "Quiz.Answer3")
txtAnswer4.DataBindings.Add("Text", QuizDataSet, "Quiz.Answer4")

The code below shows what I've done to stop the quiz after 10 questions have been answered. The bit with "BindingContext.." is how the questions progress once the forward button is clicked.

Answered = 0

Private Sub cmdForward_Click(sender As Object, e As EventArgs) Handles cmdForward.Click

        BindingContext(QuizDataSet, "Quiz").Position = BindingContext(QuizDataSet, "Quiz").Position + 1

Answered = Answered + 1

If Answered = 10 Then
    End
End If 

So far, the output of the text boxes has been very linear - it starts from QNumber 1, moves onto QNumber 2, etc. and goes to QNumber 10. I want it to be random. For example, it starts at QNumber 15, moves onto QNumber 6 and then ends on QNumber 19 or something like that.

I would assume I would have to use .Position, shown beforehand, but again, I'm not really sure so any help would be very much appreciated.

Answer 1

You can modify your SQL query to include a pseudo-random value for each record:

Select *, Rnd(-Timer() * [ID]) As RandomNumber 
From YourTable

where ID is the primary key. Then sort on RandomNumber.

Or, you can let the query sort:

Select *
From YourTable
Order By Rnd(-Timer() * [ID]);