How can you compare two textboxes in MS Access?

Question

Hello I'm new to this site so sorry if I do something wrong,feel free to correct me :D So here is my question: I'm making some kind of quiz in MS Access and I'm stuck .I've put textboxes on a form (20 of them) and using this code I called the values in txtboxes from table that I made.

Dim a As Integer
Dim txtbox(19) As TextBox

For a = 1 To 20
Controls("Txtbox" & a).Value = DLookup("Question", "questions_and_answers", "[questions_and_answers]![questionID]=Int((20 - 1 + 1) * Rnd + 1)")
Next a

Now I don' want the values to duplicate so I need 20 different values(questions) called and I dont know how ,any help?

Instead of DLookup you can randomly sort a query and then iterate over the results like over here https://stackoverflow.com/questions/9937222/how-to-get-random-record-from-ms-access-database . The query might look like `SELECT TOP 20 [Question] FROM [questions_and_answers] ORDER BY Rnd(-(100000*questionID)*Time())` — Jeremy Cook, Oct 22 '20 at 15:08

score 0 · Accepted Answer · answered Oct 22 '20 at 14:43

If i understand correctly you want those 20 questions to be shuffled each time you open the form? One simple way to not allow dublicates when you use random numbers is storing already saved numbers. You can initialize two variables:

dim strUsedQuestions as string :strUsedQuestions =",0,"
dim rndNum as integer

And change the loop to:

For a = 1 To 20
    Do while instr( 1,   strUsedQuestions, "," & rndNum & ",") > 0 then ' change the number until it is new question
       rndNum = Int((20 - 1 + 1) * Rnd + 1)
   Loop

   Controls("Txtbox" & a).Value = DLookup("Question", "questions_and_answers", "[questions_and_answers]![questionID]=" & rndNum )
   strUsedQuestions = strUsedQuestions  & rndNum & ","
Next a

However this is a little tricky and risky. If you don`t have enough questions the code will be in endless loop!

Thank you ,for now it works .I will add more than 20 questions and if I ran into problem then im sure it will be much easier for me to find a solution :D — aaa, Oct 23 '20 at 12:57

Attila · Answer 2 · 2020-10-22T14:53:35.403

You need 20 unique numbers, just calling Rnd() is not enough. You can try this code below - may be not the most efficient but probably works for you:

Dim I As Integer, J As Integer
Dim Rands(1 To 20) As Integer, Tmp As Integer
Dim a As Integer

' 20 number
For I = 1 To 20
    Rands(I) = I
Next
' shuffle them
For I = 1 To 20
    J = Int(20 * Rnd + 1)
    Tmp = Rands(1)
    Rands(1) = Rands(J)
    Rands(J) = Tmp
Next
' go on
For a = 1 To 20
Controls("Txtbox" & a).Value = DLookup("Question", "questions_and_answers", "[questions_and_answers]![questionID]=" & Rands(a))
Next a

How can you compare two textboxes in MS Access?

2 Answers2