code for a questionair

[Red via AccessMonster.com]

Good Day all,

I am trying to write code behind a questionair. As you will be able to tell,
I am very unexperienced at writing code so please bare with me (and laugh at
my code :>) ) but not to my expectation I cant get it to run. Here is my
problem.

I have a questionaire that has yes/no answers. On two occasions if the
answere is yes, this brings up a set of sub-questions relating to that
question. The questions are stored in a table as is the answers stored in a
table with the questionID as the PK.

I have included my code so far (which doesn't work :<( ) . Can anyone take a
look at it and give me some guidance on where I'm going wrong? Question 708
and 785 are the two questions that have sub-questions if answered yes. If I
need to provide more information please let me know that as well.
(I tried to write code in two different ways but no good.)

Thanks in advance for you assistance.

VR,

Red

Private Sub Command22_Click()
'next Question Button
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.Bookmark = Me.Bookmark
On Error GoTo Err_Command22_Click

'question has sub questions if answered "yes"
If Me.QuestionID = 708 And True Then
DoCmd.GoToRecord , , acNext
Else
Recordset.moveto QuestionID = 785, , acNext

End If

If Me.QuestionID = 785 And True Then
DoCmd.GoToRecord , , acNext
Else
GoToQuestionID = 710, , acNext

End If

' question has sub questions if answered "yes"
'If Me.QuestionID = 785 And Yes Then
'DoCmd.GoToRecord 786 - 789
' Else
' DoCmd.GoToRecord , , acNext

Err_Command22_Click:
MsgBox "You've Reached The End Of The Review"

'End If

End Sub

 

[Nick via AccessMonster.com]

Red,

First a couple of questions:
Does the code go to records at all / at the wrong time? Or will the code not
even select the correct records?
Are there any errors when you run this?

As far as your code goes, I saw a couple of things:

'question has sub questions if answered "yes"
If Me.QuestionID = 708 And True Then

The 'And' operator makes this line be evaluated in the following way:
If (QuestionID = 708) And (True)
Which means that the second part is always true, so any time the question ID
is 708, it will see that all parts of the If statement are satisfied.

If you want to test whether the Question's answer was true, you would have
something like:

If Me.QuestionID = 708 And Me.[Your field name here] = True Then

Also, before the Err_Command22_Click label, add in 'Exit Sub'. You don't
want the message box to show unless the user really is at the end of the
review.

End If

Exit Sub

Err_Command22_Click:
MsgBox "You've Reached The End Of The Review"

Hope I helped a bit.

-Nick

 

[Red via AccessMonster.com]

Thanks Nick,

I guess that is one reason that the code doesn't run is the And True
statement. What you said makes great sense. I'll change that. Also thanks
for the Message box comment because that is exactly what it does is after
each question, the message box does appear.

What is happening is when I run it, I get question 1 then 1a (sub-question)
and that is it. There should be another 12 questions. If the answer to #1
is no, it still goes to 1a vs question 2. I dont get any errors when I run
it it just stops after question 1a.

Hope this helps you and I will make the changes you suggested.

Red

Red,

First a couple of questions:
Does the code go to records at all / at the wrong time? Or will the code not
even select the correct records?
Are there any errors when you run this?

As far as your code goes, I saw a couple of things:

'question has sub questions if answered "yes"
If Me.QuestionID = 708 And True Then

The 'And' operator makes this line be evaluated in the following way:
If (QuestionID = 708) And (True)
Which means that the second part is always true, so any time the question ID
is 708, it will see that all parts of the If statement are satisfied.

If you want to test whether the Question's answer was true, you would have
something like:

If Me.QuestionID = 708 And Me.[Your field name here] = True Then

Also, before the Err_Command22_Click label, add in 'Exit Sub'. You don't
want the message box to show unless the user really is at the end of the
review.

End If

Exit Sub

Err_Command22_Click:
MsgBox "You've Reached The End Of The Review"

Hope I helped a bit.

-Nick

 

[Red via AccessMonster.com]

Hi Nick,

I ran the code and did get an error. It said, "Compile Error, Method or
datamember not found" I'm assuming this menas that its not seeing the data?

Red

Thanks Nick,

I guess that is one reason that the code doesn't run is the And True
statement. What you said makes great sense. I'll change that. Also thanks
for the Message box comment because that is exactly what it does is after
each question, the message box does appear.

What is happening is when I run it, I get question 1 then 1a (sub-question)
and that is it. There should be another 12 questions. If the answer to #1
is no, it still goes to 1a vs question 2. I dont get any errors when I run
it it just stops after question 1a.

Hope this helps you and I will make the changes you suggested.

Red

Red,

[quoted text clipped - 33 lines]

-Nick

 

[Red via AccessMonster.com]

Sorry, more info

It errored out on Me.QuestionID = 708 and the QuestionID is hilighted in
yellow.

Hope this helps

Red

Hi Nick,

I ran the code and did get an error. It said, "Compile Error, Method or
datamember not found" I'm assuming this menas that its not seeing the data?

Red

Thanks Nick,

[quoted text clipped - 17 lines]

 

[nickf123 via AccessMonster.com]

Red

It errored out on Me.QuestionID = 708 and the QuestionID is hilighted in
yellow.

What is the entire line of code that it errors out on, and what error number
and message does it display?

Also, what are the field names in your Question table?

I think I know how to solve your problem, once I have those questions
answered I'll post some code that *should* work pretty well.

-Nick

 

[Red via AccessMonster.com]

Thanks Nick for the reply,

Here is the line of code it errors on;
If Me.QuestionID = 708 And Me.AnswerID = 1741 Then
DoCmd.GoToRecord , , acNext

The error message is "Compile Error, Method or
datamember not found"

The field name in the table is QuestionID (Question number)
The field name in the table is AnswerID (Answer ID)

Any other information you might need please ask. It is appreciated.

Red

 

[Nick via AccessMonster.com]

Red,

Here is the line of code it errors on;
If Me.QuestionID = 708 And Me.AnswerID = 1741 Then
DoCmd.GoToRecord , , acNext

I think I am still confused on the setup; tell me if I am thinking of this
correctly.

The user will see the question associated with QuestionID 708, and they will
select Yes/No in a checkbox (?), and depending on the answer, they will be
sent to the next question. Is that correct? I think I might not be seeing
how the AnswerID field is working in your table.

That being said, I created a form and wrote this routine based on my above
assumption:

First, I created a table called tblQuestions. It has 2 fields, one named
"QuestionID" (Primary Key, autonumber), the other named "Question" (holds the
actual question).

For the form:
Under the form's properties window, in the "All" tab, in the "Record
Source" property, I wrote:
"SELECT tblQuestions.* FROM tblQuestions;"

I placed a textbox named QuestionID on the form, and set its Visible
property to False. Under the properties window, in the "All" tab, in the
"Control Source" property, I wrote:
"QuestionID"

I placed a textbox named Question on the form. Under the properties window,
"All" tab, in "Control Source", I wrote:
"Question"

I added an option group and used the wizard to create two options: "Yes"
and "No". "Yes" has an option value of 1, "No" has an option value of 2.
The option group's name is "Frame1".

Finally I added a command button to confirm the answer. This is the code
behind the command button.

___________________________________________________
Private Sub Command8_Click()

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.Bookmark = Me.Bookmark

'This is the question ID that should be the end of the review
If Me.questionid = 8 Then
MsgBox "You have reached the end of the review.", vbInformation
Exit Sub
End If

'If the question ID is 1 and "Yes" is selected, will goto a sub-question
'Assumes that question 9 is the sub-question for this case
If Me.questionid = 1 And Frame1.Value = 1 Then
rst.FindFirst ("QuestionID = " & 9)
Me.Bookmark = rst.Bookmark
Exit Sub

'If on sub-question 9, will return to the next question in the series
ElseIf questionid = 9 Then
rst.FindFirst ("QuestionID = " & 2)
Me.Bookmark = rst.Bookmark
Exit Sub

'If the question ID is 5 and "Yes" is selected, goes to the next sub-question
'Assumes question 10 is the sub-question
ElseIf Me.questionid = 5 And Frame1.Value = 1 Then
rst.FindFirst ("QuestionID = " & 10)
Me.Bookmark = rst.Bookmark
Exit Sub

'If on sub-question 10, will return to the original sequence
ElseIf questionid = 10 Then
rst.FindFirst ("QuestionID = " & 6)
Me.Bookmark = rst.Bookmark
Exit Sub

'If there are no subquestions, will just continue with the correct sequence
Else
DoCmd.GoToRecord , , acNext
Exit Sub
End If

End Sub
__________________________________________________


That code worked great for me... I hope it is what you were needing. Reply
back with an update... If it isn't what you needed, it's no problem, I don't
mind an exercise every now and then.

HTH,

-Nick

 

[Red via AccessMonster.com]

Thanks Nick,
Sorry for the delay in responding to you. Your concept was spot on andy our
solution with only a bit of tweaking worked brilliantly. Thanks for taking
the time to help me out it is appreciated more then you will know.

VR,

Red