Query to select a correct answer and then produce msg box.

  • Thread starter Thread starter MARTIN
  • Start date Start date
M

MARTIN

Hi
I am working on a database that asks questions and gives 4
options, A B C and D to choose from.
The questions are set up in a table called tbl_questions.
within this table the correct answers are contained in a
field called 'key'. Therefore each question has a field
called 'key' with a value in of either A, B C or D.
What I need now is a way to run a query so when the
student clicks on a button 'A' the query runs and if 'A'
is the correct answer then a message box comes up
saying 'Correct', if not a message box comes up
say 'wrong'.
Hope someone can help.
 
I would recommend the use of the DLookUp function as a way of solving this
problem.

Set up an option group (optAnswer) on the form with the four possible
answers 'A' , 'B' , 'C' , 'D' equating the values 1,2,3 and 4. The, in the
option groups AfterUpdate eventHandler, put in code along the lines of

strAnswer = DLookUp('[key]' , 'tblQuestions' , 'questionId = ' &
txtQuestionId)
Select Case optAnswer
Case 1
If strAnswer = "A" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
Case 2
If strAnswer = "B" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
Case 3
If strAnswer = "C" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
Case 4
If strAnswer = "D" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
End Select
MsgBox strMessage, vbOkOnly

As you can see, you will need to hold the primary key of the current
question somewhere on the form; either in a text box (as in the code above)
or as a variable.

Hope This Helps
Gerald Stanley MCSD
 
Thanks for that, but can you break it down even further
for me as I am only a beginner.
In my tbl_questions the correct option (i.e A B C or D) is
contained in field 'Key'.
The main form where the questions will appear with the
options is called frm_main.
Do I need to set up command buttons each for A B C, and D?



-----Original Message-----
I would recommend the use of the DLookUp function as a way of solving this
problem.

Set up an option group (optAnswer) on the form with the four possible
answers 'A' , 'B' , 'C' , 'D' equating the values 1,2,3 and 4. The, in the
option groups AfterUpdate eventHandler, put in code along the lines of

strAnswer = DLookUp
Click to expand...
('[key]' , 'tblQuestions' , 'questionId = ' &
 
Stage 1 - Table Design
I am assuming that the structure of your table is something like
- questionId - the primary key, probably an autonumber
- questionText - the question itself
- answerA - the text of answer A
- answerB
- answerC
- answerD
- key - the letter indicating the correct answer

Stage 2 - Form Design
I am assuming that the recordSource for frm_main is the above table. By
placing the form in 'Design' mode, you can drop the columns questionId,
questionText, answerA, answerB, answerC and answerD onto the form. As you do
not wish the user to change any of this information, you should set the
Enabled property to False and the Locked property to True. You could also
set the qusetionId Visible property to False if you did not wish the user to
see this.

Then, from the ToolBox toolbar, you should highlight the 'Option Group' tool
(it is represented as a box with XYZ on the top) and place it on the form to
invoke the wizard. The first thing that the wizard will ask you for is the
Label Names and you should enter A as the first label, B as the send, C then
D.
Clicking on the Next button, you will be asked for the default and you
should select 'No, I don't want a default'.
Clicking on the Next button, you will be asked for the values to be assigned
to each of the options. It should default to A=1, B=2, C=3 and D=4, but if
not, you should set it to these values.
Clicking on the Next button, you will be asked for type of controls that you
would like to see. It should default to Option Buttons, which would be the
standard for this type of application.
Clicking on the Next button and you will be asked for the Caption that you
would like to see. This could be something like 'Your Answer'.
Click on Finish and you are done.

Stage 3 - Adding the Code
With the form still in Design mode, you should be able to click on the Code
Icon to open up the Visula Basic editor. You need to select the option group
control in the first dropdown (its name will something like Frame0), then
AfterUpdate in the second dropdown. In the main body, you should now see the
lines
Private Sub Frame0_AfterUpdate()

End Sub
and it is in here taht the code needs to be inserted. The code should look
something like

Dim strAnswer As String
Dim strMessage As String
strAnswer = DLookUp('[key]' , 'tblQuestions' , 'questionId = ' &
questionId)
Select Case Frame0
Case 1
If strAnswer = "A" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
Case 2
If strAnswer = "B" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
Case 3
If strAnswer = "C" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
Case 4
If strAnswer = "D" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
End Select
MsgBox strMessage, vbOkOnly

I hope this explanation is suitably detailed for you.


Martin said:
Thanks for that, but can you break it down even further
for me as I am only a beginner.
In my tbl_questions the correct option (i.e A B C or D) is
contained in field 'Key'.
The main form where the questions will appear with the
options is called frm_main.
Do I need to set up command buttons each for A B C, and D?



-----Original Message-----
I would recommend the use of the DLookUp function as a way of solving this
problem.

Set up an option group (optAnswer) on the form with the four possible
answers 'A' , 'B' , 'C' , 'D' equating the values 1,2,3 and 4. The, in the
option groups AfterUpdate eventHandler, put in code along the lines of

strAnswer = DLookUp
Click to expand...
('[key]' , 'tblQuestions' , 'questionId = ' &
txtQuestionId)
Select Case optAnswer
Case 1
If strAnswer = "A" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
Case 2
If strAnswer = "B" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
Case 3
If strAnswer = "C" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
Case 4
If strAnswer = "D" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
End Select
MsgBox strMessage, vbOkOnly

As you can see, you will need to hold the primary key of the current
question somewhere on the form; either in a text box (as in the code above)
or as a variable.

Hope This Helps
Gerald Stanley MCSD

.
Click to expand...
Click to expand...
 
Hi
I am still having issues with this, can I email you what I
have done so far?
-----Original Message-----
Stage 1 - Table Design
I am assuming that the structure of your table is something like
- questionId - the primary key, probably an autonumber
- questionText - the question itself
- answerA - the text of answer A
- answerB
- answerC
- answerD
- key - the letter indicating the correct answer

Stage 2 - Form Design
I am assuming that the recordSource for frm_main is the above table. By
placing the form in 'Design' mode, you can drop the columns questionId,
questionText, answerA, answerB, answerC and answerD onto the form. As you do
not wish the user to change any of this information, you should set the
Enabled property to False and the Locked property to True. You could also
set the qusetionId Visible property to False if you did not wish the user to
see this.

Then, from the ToolBox toolbar, you should highlight the 'Option Group' tool
(it is represented as a box with XYZ on the top) and place it on the form to
invoke the wizard. The first thing that the wizard will ask you for is the
Label Names and you should enter A as the first label, B as the send, C then
D.
Clicking on the Next button, you will be asked for the default and you
should select 'No, I don't want a default'.
Clicking on the Next button, you will be asked for the values to be assigned
to each of the options. It should default to A=1, B=2, C=3 and D=4, but if
not, you should set it to these values.
Clicking on the Next button, you will be asked for type of controls that you
would like to see. It should default to Option Buttons, which would be the
standard for this type of application.
Clicking on the Next button and you will be asked for the Caption that you
would like to see. This could be something like 'Your Answer'.
Click on Finish and you are done.

Stage 3 - Adding the Code
With the form still in Design mode, you should be able to click on the Code
Icon to open up the Visula Basic editor. You need to select the option group
control in the first dropdown (its name will something like Frame0), then
AfterUpdate in the second dropdown. In the main body, you should now see the
lines
Private Sub Frame0_AfterUpdate()

End Sub
and it is in here taht the code needs to be inserted. The code should look
something like

Dim strAnswer As String
Dim strMessage As String
strAnswer = DLookUp
Click to expand...
('[key]' , 'tblQuestions' , 'questionId = ' &
questionId)
Select Case Frame0
Case 1
If strAnswer = "A" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
Case 2
If strAnswer = "B" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
Case 3
If strAnswer = "C" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
Case 4
If strAnswer = "D" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
End Select
MsgBox strMessage, vbOkOnly

I hope this explanation is suitably detailed for you.


Martin said:
Thanks for that, but can you break it down even further
for me as I am only a beginner.
In my tbl_questions the correct option (i.e A B C or D) is
contained in field 'Key'.
The main form where the questions will appear with the
options is called frm_main.
Do I need to set up command buttons each for A B C, and D?



-----Original Message-----
I would recommend the use of the DLookUp function as a way of solving this
problem.

Set up an option group (optAnswer) on the form with
Click to expand...
the
four possible
answers 'A' , 'B' , 'C' , 'D' equating the values
Click to expand...
1,2,3
and 4. The, in the
option groups AfterUpdate eventHandler, put in code
Click to expand...
along
the lines of
strAnswer = DLookUp
Click to expand...
('[key]' , 'tblQuestions' , 'questionId = ' &
txtQuestionId)
Select Case optAnswer
Case 1
If strAnswer = "A" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
Case 2
If strAnswer = "B" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
Case 3
If strAnswer = "C" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
Case 4
If strAnswer = "D" Then
strMessage = "Correct"
Else
strMessgae = "Wrong"
End If
End Select
MsgBox strMessage, vbOkOnly

As you can see, you will need to hold the primary key
Click to expand...
of
the current
question somewhere on the form; either in a text box
Click to expand...
(as
in the code above)
or as a variable.

Hope This Helps
Gerald Stanley MCSD
:

Hi
I am working on a database that asks questions and gives 4
options, A B C and D to choose from.
The questions are set up in a table called tbl_questions.
within this table the correct answers are contained
Click to expand...
in
a
field called 'key'. Therefore each question has a field
called 'key' with a value in of either A, B C or D.
What I need now is a way to run a query so when the
student clicks on a button 'A' the query runs and if 'A'
is the correct answer then a message box comes up
saying 'Correct', if not a message box comes up
say 'wrong'.
Hope someone can help.

.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top