check for null in option group on continuous subform

[tuxalot]

The project is a modification of Duane Hookom's At Your Survey and questions are presented in a series of subforms each on a tab control. I would like to make a label visible on the main form with a colored background when the questions for a given tab have all been answered. As questions are answered a series of colored labels will appear, showing the user how close to completion they are. So on the tabs change event I have a select case that runs code checking for null in the option group of the continuous subform.

main form: frmSurveyResponses (tab control is here)
subform: sfrmResponses
option group name: grpRspns1 (on subform)
label name: lblComplete1 (on frmSurveyResponses)

      Dim ctl As Control
      For Each ctl In Me.sfrmResponses.Form.Controls
         If IsNull(Me!sfrmResponses.Form!grpRspns1) Then
             Me!lblComplete1.Visible = False
                Else
             Me!lblComplete1.Visible = True
         End If           
      Next ctl

So this works but the label becomes visible after the first question is answered and I want it to only become visible when all questions have been answered.

For reference, the recordsource for the subform is:

SELECT DISTINCTROW tblResponses.*, tblQuestions.QstnText, tblQuestions.QstnLvl1, tblQuestions.QstnIsActive, tblQuestions.SectionID
FROM tblResponses RIGHT JOIN tblQuestions ON tblResponses.QstnID = tblQuestions.QstnID
WHERE (((tblQuestions.QstnIsActive)=True) AND ((tblQuestions.SectionID)=1))
ORDER BY tblQuestions.QstnLvl1;

Responses are stored in tblResponses with RspnsID and QstnID as the PK, so the data looks like this:

RspnsID   QstnID  Rspns
100         20      1
100         21      1
100         22      2
101         20      2
101         21      1
101         22      1
102         20        
102         21        
102         22          

and so on...

I also looked into a Dsum solution since all values in tblResponses are null for a given RspnsID until they are answered but to be frank, I am still an Access greenhorn and am not sure how to accomplish this.

I would really appreciate any help you can offer.

 

[dhookom]

I believe you would need to use DCount().

  =DCount("Rspns","tblResponses","RspnsID=" & [RspnsID] )

Duane
Hook'D on Access
MS Access MVP

 

[tuxalot]

Thanks Duane. Got it going.