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)
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
Me!lblComplete1.Visible = True
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:
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 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:
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
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):
Private Sub Command_CheckCompleted_Click()
Me.Label_Completed.Visible = Me.Child_SF_Tab0.Form.Completed
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.