Force update into unbounded comments field

[lfriend]

Hopefully there's someone who can help me with this!!! I've had several unsuccesfull attempts and don't know what else to try...

We're trying to force users to make entries in a field called txtComments in the frmQuestionnaire form if they provide negative responses and to stay on that record until the comments are changed. Inside the form is an option group (fraResponses) which contains options (OptA, OptB..etc.) valued from 1 through 7, based on a likert 7 scale. We're only using a likert 6 scale, so the respondents will only see 2-7 on the form. If they options B-D (valued 2-4), they are considered negative, so we want to make sure they enter a comment.

I tried to enter the italicized code under the form on BeforeUpdate, AfterUpdate, CmdUp (click to next question) and CmdDown (click to previous question), with frmQuestionnaire but nothing happens:

There is a module called clsUser which contains the following and where I tried to insert the code:

Set myForm = Forms!frmQuestionnaire
' ====================================================
' First, set the value of the variables
' ====================================================
lngQuestion = arrQ(lngArray, 0)
lngSession = GetCustomInfo("TestSession")
lngUser = UserID
lngBillet = BilletID

strComment = Nz(myForm.txtComment, "")

strComment = "None"

'If user selects negative responses
If Form_frmQuestionnaire.fraResponses.OptionValue = 2 Or 3 Or 4 And strComment = "None" Then
MsgBox "Please explain the problems you encountered with the system which " & _
"caused you to select an unfavorable response."
Form_frmQuestionnaire.txtComment.SetFocus

End If

lngResponse = myForm.fraResponses


When I place the code there, the dialog box appears for all values and goes to the next record. This is what appears for the CmdUp (click to next question):

If cUser.blnDirty = False And Me.fraResponses = 153 (this is default value for the entire option group) And cUser.blnNew = True Then
cUser.blnDirty = True
End If

cUser.CaptureAnswer
If cUser.lngArray < cUser.UBound_ArrQ() Then
cUser.lngArray = cUser.lngArray + 1
Else
cUser.lngArray = cUser.UBound_ArrQ()
End If
cUser.FillQuestions
cUser.blnDirty = False

I've tried to enter the line of code with the form and the module, but no luck.

I also want it to remain on the same record until the field is changed. The form is unbound and has the following:

Public Sub FillQuestions()


'*****************************************************
' Purpose: Scroll through the questions
'
' Assumptions: N/A
'
' Effects: N/A
'
' Inputs:
' None
'
' Returns:
' None
'*****************************************************

Dim lngRG As Long

Set myForm = Forms!frmQuestionnaire
myForm.txtQuestion = arrQ(lngArray, 1)
lngRG = arrQ(lngArray, 2)
myForm.prgProgress.Value = lngArray + 1
myForm.lblProgText.Caption = "Question " & (lngArray + 1) & " of " & lngQuestions
myForm.lblTS.Caption = "Test Session: " & arrQ(lngArray, 3)
myForm.lblQID.Caption = "Question ID: " & arrQ(lngArray, 0)

GetResponseSet lngRG
FillAnswers
If lngArray = 0 Then
myForm.txtComment.SetFocus
myForm.cmdDown.Enabled = False
Else
myForm.cmdDown.Enabled = True
End If

If lngArray >= UBound(arrQ) Then
myForm.txtComment.SetFocus
myForm.cmdUp.Enabled = False
Else
myForm.cmdUp.Enabled = True
End If
End Sub

Public Sub FillAnswers()
'*****************************************************
' Purpose: Fill out the user's previous responses
'
' Assumptions: N/A
'
' Effects: N/A
'
' Inputs:
' None
'
' Returns:
' None
'*****************************************************

Dim strSQL As String
Dim recAnswer As New ADODB.Recordset

Set myForm = Forms!frmQuestionnaire
strSQL = "SELECT datResponse.reDatQuestionID, datResponse.reDatRespondentID, "
strSQL = strSQL & "datResponse.reDatResponseSetID, datResponse.reComment "
strSQL = strSQL & "FROM datResponse "
strSQL = strSQL & "WHERE datResponse.reDatQuestionID=" & arrQ(lngArray, 0)
strSQL = strSQL & "AND datResponse.reDatRespondentID=" & UserID

recAnswer.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

If Not recAnswer.EOF Then
myForm.fraResponses = recAnswer!reDatResponseSetID
myForm.txtComment = recAnswer!reComment
blnNew = False
Else
If myForm.fraResponses <> 152 Then
myForm.fraResponses = 153
myForm.txtComment = ""
blnNew = True
End If
End If

recAnswer.Close
Set recAnswer = Nothing

End Sub

Thanks in advance for your help!!

 

[phoenicks7]

Well, I had a bit of a time following all of your code and the fact that it's in a module... so I'm not sure what the structure of your form is....

But I'll make an educated guess that the user uses a button to submit their information... I'll also guess at a few field names.. but you should get the general idea.

On the form.....

Private Sub cmdSubmit_Click()
If optScore < 5 then ' Check Score to see if bad
' Score is negative.. we require comments
If txtComments="" then
MsgBox ("Score is poor, comments required")
Exit Sub ' Exits Sub without processing your data
End If
End If

GoTo SubmitInfo ' Either optScore >=5 or Comments was not Null
SubmitInfo:
' Do whatever you want with data now that it's complete
End Sub