Please help me on my code

[lovedieer]

I am working on a database with survey question.
The form include main form with demographich info and a subform with survey questions.

My logic is : for all questions, the default answer is Yes, it can be changed to No or Null. However, if you change a question with No answer, you cannot go back to Yes or null. If you check a question with No answer, a form will pop up which allow you to add comments.

It works fine for a while, however, a big problem was found one day:
If the participant 1 check certain questions with NO, the rest of the participants cannot check No for the same question except for question 1. Sound weird!

the code for question subform:


Option Compare Database
Option Explicit
Private Sub cbQ1_AfterUpdate()
If [cbQ1] = "No" Then
[QuestionNumber] = 1
[cbQ1].Locked = True
DoCmd.OpenForm "frmNoResponse", , , "[ParticipantID] = Forms!Demo![ParticipantID]"
End If
End Sub
Private Sub cbQ1_DblClick(Cancel As Integer)
If [cbQ1] <> "No" Then
[cbQ1] = " "
End If
End Sub
Private Sub cbQ2_AfterUpdate()
If [cbQ2] = "No" Then
[QuestionNumber] = 2
[cbQ2].Locked = True
DoCmd.OpenForm "frmNoResponse", , , "[ParticipantID] = Forms!Demo![ParticipantID]"
End If
End Sub
Private Sub cbQ2_DblClick(Cancel As Integer)
If [cbQ2] <> "No" Then
[cbQ2] = " "
End If
End Sub
(same for the rest of the questions)

Private Sub Form_Current()
On Error GoTo Err_Form_Current
[QuestionNumber] = Null

If [ParticipantID] = Null Then
[cbQ1].Locked = False
[cbQ2].Locked = False
End If

Select Case [ParticipantID] > 0
Case [cbQ1] = "Yes" Or IsNull([cbQ1]) Or [cbQ1] = " "
[cbQ1].Locked = False
Case [cbQ1] = "No"
[cbQ1].Locked = True
Case [cbQ2] = "Yes" Or IsNull([cbQ2]) Or [cbQ2] = " "
[cbQ2].Locked = False
Case [cbQ2] = "No"
[cbQ2].Locked = True


(same for the other questions)

End Select


Exit_Form_Current:
Exit Sub

Err_Form_Current:
MsgBox Err.Description
Resume Exit_Form_Current

End Sub
Private Sub Form_Load()
Me.Repaint
End Sub

 

[ajetrumpet]

put your code in code tags and tab it out, it is not readable.

 

[Kafrin]

OK, try using this code instead of all the code you posted. This simplifies what you're doing a bit and helps to make sure that you definitely have the same code for each question. Note that I'm assuming you don't have more than 255 questions and so can use a Byte for the question number.

It would also help to know what QuestionNumber is - is it another control on the form or a public variable?

[COLOR=black]Option Compare Database[/COLOR]
[COLOR=black]Option Explicit[/COLOR]
 
[COLOR=black]Private Sub LockQ(QNum As Byte)[/COLOR]
[COLOR=black] Dim QCtrl As String[/COLOR]
 
[COLOR=black] QCtrl = "cbQ" & QNum[/COLOR]
[COLOR=black] If Me(QCtrl) = "No" Then[/COLOR]
[COLOR=black]     QuestionNumber = QNum[/COLOR]
[COLOR=black]     Me(QCtrl).Locked = True[/COLOR]
[COLOR=black]     DoCmd.OpenForm "frmNoResponse", , , "[ParticipantID] = " & Me!ParticipantID[/COLOR]
[COLOR=black] Else[/COLOR]
[COLOR=black]     Me(QCtrl).Locked = False[/COLOR]
[COLOR=black] End If[/COLOR]
[COLOR=black]End Sub[/COLOR]
 
[COLOR=black]Private Sub cbQ1_AfterUpdate()[/COLOR]
[COLOR=black] LockQ 1[/COLOR]
[COLOR=black]End Sub[/COLOR]
[COLOR=black]Private Sub cbQ2_AfterUpdate()[/COLOR]
[COLOR=black] LockQ 2[/COLOR]
[COLOR=black]End Sub[/COLOR]
[COLOR=green]'and so on for other questions[/COLOR]
 
Private Sub[COLOR=black][COLOR=black] SetQ(QNum As Byte)[/COLOR]
[COLOR=black] Dim QCtrl As String[/COLOR]
 
[COLOR=black] QCtrl = "cbQ" & QNum[/COLOR]
[COLOR=black] If Me(QCtrl) <> "No" Then Me(QCtrl) = " "[/COLOR]
 
[COLOR=black]End Sub[/COLOR]
[/COLOR]
[COLOR=black][COLOR=black]Private Sub cbQ1_DblClick(Cancel As Integer)[/COLOR]
    SetQ 1
End Sub[/COLOR]
[COLOR=black][COLOR=black]Private Sub cbQ2_DblClick(Cancel As Integer)[/COLOR]
    SetQ 2
End Sub
[/COLOR][COLOR=black][COLOR=green]'and so on for other questions[/COLOR]
 
[/COLOR][COLOR=black]Private Sub Form_Current()[/COLOR]
[COLOR=black]On Error GoTo Err_Form_Current[/COLOR]
    Dim C as Control
 
[COLOR=black] QuestionNumber = Null[/COLOR]
 
    For Each C In Me.Controls
        If C.Name Like "cbQ*" Then
            C.Locked = (C = "No" And Not Me.NewRecord)
        End If
    Next C
 
[COLOR=black]Exit_Form_Current:[/COLOR]
[COLOR=black] Exit Sub[/COLOR]
 
[COLOR=black]Err_Form_Current:[/COLOR]
[COLOR=black] MsgBox Err.Description[/COLOR]
[COLOR=black] Resume Exit_Form_Current[/COLOR]
[COLOR=black]End Sub[/COLOR]

Also note that the line:
C.Locked = (C = "No" And Not Me.NewRecord)
can probably just be:
C.Locked = (C = "No")
as long as your cbQ fields do default to "Yes", but I've put in the second part just to make sure.

 

[lovedieer]

Thank you for the code.
The questionNumber is a control on a pop-up form. If I check the question with "No", a comment form will pop up with the ParticipantID and questionnumber at the top.

 

[Kafrin]

In that case you may need to change the first sub to:

Private Sub LockQ(QNum As Byte)
[COLOR=black] Dim QCtrl As String, Frm As String[/COLOR]
 
[COLOR=black] QCtrl = "cbQ" & QNum[/COLOR]
Frm = "frmNoResponse"
[COLOR=black] If Me(QCtrl) = "No" Then[/COLOR]
[COLOR=black]     Me(QCtrl).Locked = True[/COLOR]
[COLOR=black]     DoCmd.OpenForm Frm, , , "[ParticipantID] = " & Me!ParticipantID[/COLOR]
[COLOR=black]     forms(Frm)!QuestionNumber = QNum[/COLOR]
[COLOR=black]     forms(Frm)!Comment.SetFocus    '<------Make sure you change Comment to the name of your comment box
 Else[/COLOR]
[COLOR=black]     Me(QCtrl).Locked = False[/COLOR]
[COLOR=black] End If[/COLOR]
[COLOR=black]End Sub

[/COLOR]