check for null in option group on continuous subform

tuxalot · #1 (**permalink**) 11-09-10, 13:53

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.

Sinndho · #2 (**permalink**) 11-13-10, 18:28

I first guess that there are several option groups in each subforms. if you want to go on with your For... Each solution, you should try something like:

a) In each subform code module, create a Property (or a Function) like this:

Code:

Public Property Get Completed() As Boolean

    Dim ctl As Control
    
    Completed = True
    For Each ctl In Me.Controls
        If ctl.ControlType = acOptionGroup Then
            If IsNull(ctl.Value) Then Completed = False
        End If
    Next
    
End Property

b) When you need to check whether every question has been answered in a subform (i.e. the subform is "completed", i.e. no OptionGroup control in the subform has a Null value) or not, you can use (here with a Command button but it's easily transposable to a Tab change):

Code:

Private Sub Command_CheckCompleted_Click()

    Me.Label_Completed.Visible = Me.Child_SF_Tab0.Form.Completed

End Sub

That way, every subform is "responsible" for its own "completeness" status and the parent form just has to ask for it. Also keep in mind that a Tab control is not a container for other controls: each control on the form remains a member of the form Controls collection wheter it is on a page of the Tab control or not.