Calculate number of true checkboxes in Continuous Form

[rpdecker]

I have found the following function and I've been able to get it working, but on my continuous form it only counts 1 even if more boxes are checked. I can not use =Abs(Sum([the field])) in the record source of a field, as the field that I need to show the calculated number is linked to a table. I am fully aware of the arguments for not recording calculated information, but this is to record the Total number of students present based on the number of "Present" checkboxes that are checked in the Continuous Subform. How can I alter this function code to get the sum of checked boxes in my continuous form.

Here is the code!

Function CountChecked() As Integer

Dim intTotalChecked As Integer
Dim ctl As Control

intTotalChecked = 0
 For Each ctl In Me
    If ctl.ControlType = acCheckBox Then
      If ctl.Value = True Then
         intTotalChecked = intTotalChecked + 1
      End If
    End If
 Next

CountChecked = intTotalChecked

End Function

Thanks in advance!

[Rabbit]

Please use code tags when posting code.

To get what you want, you'll have to either clone the recordset of the subform and loop through it and do your count that way or use a domain aggregate function like DCount() and passing in the same filter as the subform is linked on.

[rpdecker]

Rabbit thanks for the quick reply!
Unfortunately I am new to access and vba. I have heard about cloning the recordset and using DCount(), but I have no idea where to start or how the vba would be written! Can you help?

[Rabbit]

You'll probably want to read through a VBA tutorial or take a class before you take on a project like this.

As for the DCount function, it's not VBA per se, you can look at Microsoft's documentation on it.