Trigger event to set values in controls and disable them

[Ruth]

I am working on a multi-tabbed form. Each tab has a subform that
contains the questions pertinent to that section. The questions are
shown using a continuous form view. The consultant I’m doing this for
wants some questions to be “trigger” questions—If the answer is no,
then the next 4 or 5 questions would be disabled and have their answer
set to N/A. The answer control is a combo box bound to column 1 of the
choices 1;"Yes";2;"No";3;"In Progress";4;"N/A".

I’ve tried using the OnUpdate event to trigger setting the values for
question IDs that equal the questions I want to set.

Before I go through what I’ve tried, is this even possible to do?

Thanks in advance for any help!

 

[Ruth]

Thank you so much! This is certainly on the right track for what I
needed.

Here's the complicated part. But first, just a little background: I am
working in Access 2003. When a new review is started, an append query
runs to create a "blank" set of responses for all questions. Fields in
this table are Review ID, CompID, Score and Comment. By linking on
CompID to the master question table, I can display questions, filter
them by section, etc. without storing the question text multiple
times. Since the questionnaire has around 150 questions, they are
displayed one question/record per form in the continuous form view
(Sections are divided per subform via a filter and then the subform is
linked to the master form by ReviewID).

Controls on the form are: [CompID] (Unique ID for the "master"
question text - not visible), [Question] (question text), [cboScore]
(which is the combo box where the user selects yes, no, in progress or
n/a) and [CompComment] (a text field where the reviewer can enter
notes for each question. When the user tabs out of cboScore, focus
moves to CompComment in the same record (question).

The control name for the N/A value I'm trying to set is the same
control where I just selected "No". I'm thinking that I need something
like a "where" statement that would set the value for cboScore based
on the CompID. I think I'm on the right track here, but I'm using the
wrong command to move to the next record. The code stops when it gets
there, so you may see more errors that I just haven't hit yet. By the
way, "No" and "N/A" are displayed in the combo, but 2 is stored for No
4 is stored for N/A when selected.

Private Sub cboScore_AfterUpdate()
If Me.CompID = 107 Then
If Me.cboScore = "2" Then
DoCmd.GoToRecord = acNext
Where Me.CompID = 108
Me.cboScore = 4
Me.cboScore.Enabled = False
DoCmd.GoToRecord = acNext
Where Me.CompID = 109
Me.cboScore = 4
Me.cboScore.Enabled = False
Else
DoCmd.GoToRecord = acNext
Where Me.CompID = 108
Me.cboScore = ""
Me.cboScore.Enabled = True
DoCmd.GoToRecord = acNext
Where Me.CompID = 109
Me.cboScore = ""
Me.cboScore.Enabled = True
End If
End Sub

(The debugger highlights .GoToRecord = and I get a message box that
says Compile Error: Expected Function or Variable.)

Would something like that work? (And I know I would need to add the
other code you supplied but I've got to get this part worked out
first.)

Other ideas for how to approach this are welcome!

Thanks again.
Ruth

 

[Ruth]

Thanks, Linq!
You got me started. Since each question is a "record" on the subform,
I don't have individual control names for each answer. There's just
one combo box that shows up over and over. Following is what I've come
up with so far with no error on Yes, N/A or In Progress, but I get a
runtime error when I select No. I

Combo box values are stored as 1=Yes, 2=No, 3=In Progress and 4=N/A

Here's the code:
Private Sub cboScore_AfterUpdate()
If Me.CompID = 107 Then
If Me.cboScore = "2" Then
DoCmd.GoToRecord acNext
If Me.CompID = 108 Then
Me.cboScore = 4
Me.cboScore.Enabled = False
DoCmd.GoToRecord acNext
If Me.CompID = 109 Then
Me.cboScore = 4
Me.cboScore.Enabled = False
Else
DoCmd.GoToRecord acNext
If Me.CompID = 108 Then
Me.cboScore = ""
Me.cboScore.Enabled = True
DoCmd.GoToRecord acNext
If Me.CompID = 109 Then
Me.cboScore = ""
Me.cboScore.Enabled = True
End If
End If
End If
End If
End If
End If
End Sub

This is attached to the combo box's On Update event.

Is this on the right track? Other suggestions?

Thanks very much for your response!

ETA: I fixed that error message, but once that section of code ran, it
wouldn't disable the control since it was active. Moving the disable
commands to after the value has been set and focus returned to the
original record didn't work either. I'll keep working on this, but in
the meantime any other ideas will be appreciated!

Thanks!
Ruth

 

[Ruth]

Here's my latest code. It sets the value for the combo's on questions
108 and 109 and returns the cursor to the combo box for question 107.
I have not been able to figure out how to disable the controls for 108
and 109 after N/A (4) has been entered for them. Also, the If Else is
not working. No other value selection will set the combo back to null.
But no error message either!

Private Sub cboScoreFrm4_AfterUpdate()
If Me.CompID = 107 Then
If Me.cboScoreFrm4 = "2" Then
RunCommand acCmdRecordsGoToNext
If Me.CompID = 108 Then
Me.cboScoreFrm4 = 4

RunCommand acCmdRecordsGoToNext
If Me.CompID = 109 Then
Me.cboScoreFrm4 = 4

RunCommand acCmdRecordsGoToPrevious
RunCommand acCmdRecordsGoToPrevious


Else
RunCommand acCmdRecordsGoToNext
If Me.CompID = 108 Then
Me.cboScoreFrm4 = ""
' Me.cboScoreFrm4.Enabled = True
RunCommand acCmdRecordsGoToNext
If Me.CompID = 109 Then
Me.cboScoreFrm4 = ""
' Me.cboScoreFrm4.Enabled = True
End If
End If
End If
End If
End If
End If
End Sub


Thanks for any suggestions!
Ruth