Since I'm not getting any responses for the Dlookup procedure, I thought I'd check if I can achieve the same goal differently. I need to get the randomizing in the application to start at a different point each time it is started. I tried to put the Randomize, Randomize Timer and Rnd at different points in the program to no avail..
Here is the code:
'Option Compare Database
Option Explicit
Dim SQL As String
Dim RS As recordset
Dim DB As Database
Dim recordset As Connection 'me.recordset
Dim rc As recordset
Dim qt As String ' question text module-level variable
Dim ca As String ' correct answer module-level variable
-----------------------------------------------------------------
Private Sub btn_Click()
LoadQuestion
End Sub
-----------------------------------------------------------------
Private Sub Form_Load()
Randomize Timer
LoadQuestion
End Sub
-----------------------------------------------------------------
' execute the query at the source of the
' form, fill question label,
' randomize the answers, and store the correct one in
' a module-level variable
Private Sub LoadQuestion()
Set DB = OpenDatabase(App.Path & ("\MyFile.mdb"))
Set RS = DB.OpenRecordset("Select top 3 [answer] as [correct], questiontextb
as questiontext, correctanswer From
, qQuestionTextAndAnswer Where
((([CorrectAnswer]) <> [answer]))ORDER BY Rnd(
.[IDQuestion]);")
Dim op(1 To 3) As String
Dim randomArr(0 To 3) As String
Dim I As Integer, rc As Long
' clear all the labels
lblText.Caption = ""
lbl1.Caption = ""
lbl2.Caption = ""
lbl3.Caption = ""
lbl4.Caption = ""
' initialize the system randomizer, in order to randomly
' execute the query
Randomize
qt = RS.Fields("QuestionText").Value
ca = RS.Fields("CorrectAnswer").Value
RS.MoveFirst
Do While Not RS.EOF
I = I + 1
op(I) = RS.Fields("Correct").Value
RS.MoveNext
Loop
rc = I + 1
' simulates a records counter, as this recordset
' doesn't expose a RecordCount property to read
' fill the text label
lblText.Caption = qt
' randomize the four answers from the query into
' an array
I = Int((rc - 1 + 1) * Rnd + 1) - 1
' generates a random number among 0 and 3
randomArr(I) = ca
randomArr((I + 1) Mod rc) = op(1)
randomArr((I + 2) Mod rc) = op(2)
randomArr((I + 3) Mod rc) = op(3)
' fills the array values into the labels
lbl1.Caption = randomArr(0)
lbl2.Caption = randomArr(1)
lbl3.Caption = randomArr(2)
lbl4.Caption = randomArr(3)
' I'm forced to use four separate labels, as MSAccess
' doesn't support
' arrays of controls. In VB I'd create an array of four
' labels
End Sub
-----------------------------------------------------------------
Private Sub lbl1_Click()
CheckLabel lbl1
End Sub
-----------------------------------------------------------------
Private Sub lbl2_Click()
CheckLabel lbl2
End Sub
-----------------------------------------------------------------
Private Sub lbl3_Click()
CheckLabel lbl3
End Sub
-----------------------------------------------------------------
Private Sub lbl4_Click()
CheckLabel lbl4
End Sub
-----------------------------------------------------------------
' checks if the clicked label caption has the same value
' of the correct answer variable. If so, loads another
' question
' otherwise it says "Try again"
Private Sub CheckLabel(lbl As Label)
If lbl.Caption = ca Then
LoadQuestion
Else
MsgBox "Try again"
End If
End Sub
-----------------------------------------------------------------
Maybe someone has a suggestion to get a random starting point which doesn't repeat itself.
Any help would be appreciated.
Ram_son
Here is the code:
'Option Compare Database
Option Explicit
Dim SQL As String
Dim RS As recordset
Dim DB As Database
Dim recordset As Connection 'me.recordset
Dim rc As recordset
Dim qt As String ' question text module-level variable
Dim ca As String ' correct answer module-level variable
-----------------------------------------------------------------
Private Sub btn_Click()
LoadQuestion
End Sub
-----------------------------------------------------------------
Private Sub Form_Load()
Randomize Timer
LoadQuestion
End Sub
-----------------------------------------------------------------
' execute the query at the source of the
' form, fill question label,
' randomize the answers, and store the correct one in
' a module-level variable
Private Sub LoadQuestion()
Set DB = OpenDatabase(App.Path & ("\MyFile.mdb"))
Set RS = DB.OpenRecordset("Select top 3 [answer] as [correct], questiontextb
as questiontext, correctanswer From
((([CorrectAnswer]) <> [answer]))ORDER BY Rnd(
Dim op(1 To 3) As String
Dim randomArr(0 To 3) As String
Dim I As Integer, rc As Long
' clear all the labels
lblText.Caption = ""
lbl1.Caption = ""
lbl2.Caption = ""
lbl3.Caption = ""
lbl4.Caption = ""
' initialize the system randomizer, in order to randomly
' execute the query
Randomize
qt = RS.Fields("QuestionText").Value
ca = RS.Fields("CorrectAnswer").Value
RS.MoveFirst
Do While Not RS.EOF
I = I + 1
op(I) = RS.Fields("Correct").Value
RS.MoveNext
Loop
rc = I + 1
' simulates a records counter, as this recordset
' doesn't expose a RecordCount property to read
' fill the text label
lblText.Caption = qt
' randomize the four answers from the query into
' an array
I = Int((rc - 1 + 1) * Rnd + 1) - 1
' generates a random number among 0 and 3
randomArr(I) = ca
randomArr((I + 1) Mod rc) = op(1)
randomArr((I + 2) Mod rc) = op(2)
randomArr((I + 3) Mod rc) = op(3)
' fills the array values into the labels
lbl1.Caption = randomArr(0)
lbl2.Caption = randomArr(1)
lbl3.Caption = randomArr(2)
lbl4.Caption = randomArr(3)
' I'm forced to use four separate labels, as MSAccess
' doesn't support
' arrays of controls. In VB I'd create an array of four
' labels
End Sub
-----------------------------------------------------------------
Private Sub lbl1_Click()
CheckLabel lbl1
End Sub
-----------------------------------------------------------------
Private Sub lbl2_Click()
CheckLabel lbl2
End Sub
-----------------------------------------------------------------
Private Sub lbl3_Click()
CheckLabel lbl3
End Sub
-----------------------------------------------------------------
Private Sub lbl4_Click()
CheckLabel lbl4
End Sub
-----------------------------------------------------------------
' checks if the clicked label caption has the same value
' of the correct answer variable. If so, loads another
' question
' otherwise it says "Try again"
Private Sub CheckLabel(lbl As Label)
If lbl.Caption = ca Then
LoadQuestion
Else
MsgBox "Try again"
End If
End Sub
-----------------------------------------------------------------
Maybe someone has a suggestion to get a random starting point which doesn't repeat itself.
Any help would be appreciated.
Ram_son