Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    9

    Unanswered: check for null in option group on continuous subform

    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.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.
    Have a nice day!

  3. #3
    Join Date
    Mar 2009
    Posts
    9
    Good stuff Sinndho! Thanks for pointing me in the right direction. I think your option "B" will work perfectly for this project.

    Thanks again,

    Tux

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    They are not options, they are complementary. You're welcome anyway!
    Have a nice day!

  5. #5
    Join Date
    Mar 2009
    Posts
    9
    shows you how much I know So I will look into using these.

    Tux

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •