check for null in option group on continuous subform

tuxalot

Registered User.
Local time
Yesterday, 18:04
Joined
Feb 27, 2009
Messages
42
The project is a 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)

Code:
      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:
Code:
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:

Code:
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.
 

Users who are viewing this thread

Back
Top Bottom