Checklist with multiple check boxes

[KRK]

Hi all,

I'm new to access and require some help. I have created a form with multiple questions with a checkbox next to each question, and what I am trying to achieve is if all checkboxes are ticked it marks that record as complete in the form as a Yes/No in my table column.

Thanks

[Gicu]

So you don't store the individual answers (checkboxes) in the table? If you do then you don't need the "completed" column as that would be a mere calculation based on the others. But if you don't (and don't care which one was not answered) you could add some code in the BeforeUpdate event of the form to add them all up and update the "Completed" based on the result:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'lets say you have 5 checkboxes
if me.checkbox1+me.checkbox2+me.checkbox3+me.checkbox4+me.checkbox5=-5 then
    me.RecordCompleted=True
else
    me.RecordCompleted=False
end if
End SUb

Cheers,
Vlad

[KRK]

Hi Vlad, the individual checkboxes are stored in the table. What I am looking for is

If All me.checkboxes = True then
me.recordcompleted.value = Yes
End if

[Gicu]

If the checkboxes are stored in the table I dont't think you need the recordcompleted field as it can cause data integrity problems (you have to maintain the logic everywhere and every time). Instead use a calculated control with its control source set like this: iif(([CheckBox1]+[CheckBox2]+.......nr of boxes)=-nr.of boxes,True,False) (True=-1,False=0, Yes=-1,No=0).
Cheers,
Vlad

[ssanfu]

I tend to agree with Vlad, but if you really want to keep your design, there are other thing you can do.


The first thing is I would set the LOCKED and ENABLED properties for the "RecordCompleted" checkbox:

ENABLED = False
LOCKED = TRUE

This will keep anyone from manually changing the check box.

You can use the form afterupdate event as Vlad has suggested, but that usually updates the check box when the form is closing (or saved).


Another method is to write a sub to calc the number of true check boxes any time the value of a check box changes:

Private Sub CalcCheckBoxes()
    'lets say you have 5 checkboxes
    If Me.checkbox1 + Me.checkbox2 + Me.checkbox3 + Me.checkbox4 + Me.checkbox5 = -5 Then
        Me.RecordCompleted = True
    Else
        Me.RecordCompleted = False
    End If
End Sub

Then use the CLICK event of each check box to call the calc routine. Every time a check box changes value the "RecordCompleted" check box gets updated.

Private Sub Check1_Click()
    Call CalcCheckBoxes
End Sub

Private Sub Check2_Click()
    Call CalcCheckBoxes
End Sub

Private Sub Check3_Click()
    Call CalcCheckBoxes
End Sub

--------------------------------------------------------------------------------
If you named the check boxes sequentially, Me.checkbox11, Me.checkbox2, Me.checkbox3, ... , you can change the code to use looping so you don't have to type so much.
The code would look something like

Private Sub CalcCheckBoxes()
   'lets say you have 10 checkboxes
    Const MaxChkBx As Integer = 10
    Dim CkSum As Integer
   
    For i = 1 To MaxChkBx
        CkSum = CkSum + Me("checkbox" & i)
    Next i

    If Abs(CkSum) = MaxChkBx Then
        Me.RecordCompleted = True
    Else
        Me.RecordCompleted = False
    End If
End Sub

or

Private Sub CalcCheckBoxes()
    'lets say you have 10 checkboxes
    Const MaxChkBx As Integer = 10
    Dim CkSum As Integer

    For i = 1 To MaxChkBx
        CkSum = CkSum + Me("checkbox" & i)
    Next i

    Me.RecordCompleted = (Abs(CkSum) = MaxChkBx)

End Sub