Count Checkboxes

[Emma35]

Hi again all....i thought that while i'm on a roll i might as well throw this one in too



I have a form where there are several checkboxes (all bound to different fields).....i need a textbox at the end of the form which will count the amount of checkboxes which are checked (regardless of whether true or false)...can that be easily done ?

Thanks
Em

[cyanidem]

Not tested but something like this:

Dim iCounter as integer
Dim ctrl as Control
iCounter=0
For Each ctrl In form.controls 
    If typename(ctrl) = "CheckBox" Then 
        If ctrl.value = True Then 
            iCounter = iCounter+1 
        End If 
    End If
Next ctrl
Me.YourTextBox.SetFocus
Me.YourTextBox.Value=str(iCounter)

[Emma35]

Thank you (again).....i've a couple of questions. Where does that code go ? and is the line of code where you have 'YourTextBox' the only place where i substitute a name ?

Thanks

[cyanidem]

Where that code goes it's up to you really. You can have it under some button Click event, on form_current event or make a separate sub for it and call it on every checkbox Change event. It depends if you want it to count ticked checkboxes "live" or after something special happens.
And yes, you only need to put proper name for YourTextBox.

[Emma35]

When i open the Form i'm getting a message saying 'Runtime Error 2427. You entered an expression that has no value'

If ctrl.Value = True Then is highlighted in yellow

[cyanidem]

Where did you place the code? It needs to be inside some sub, like:

Private Sub SomeButton_Click()
(here goes the code)
End Sub


Can you upload your db with that code placed?

[Emma35]

I added it to the OnCurrent event of the form

Private Sub Form_Current()Dim iCounter As Integer
Dim ctrl As Control
iCounter = 0
For Each ctrl In Form.Controls
    If TypeName(ctrl) = "CheckBox" Then
        If ctrl.Value = True Then
            iCounter = iCounter + 1
        End If
    End If
Next ctrl
Me.txtCount.SetFocus
Me.txtCount.Value = Str(iCounter)
End Sub]

I've a feeling i've done something stupid

[cyanidem]

Is that exactly how it looks in your VBA editor?

Private Sub Form_Current()Dim iCounter As Integer

should be two separate lines:

Private Sub Form_Current()
Dim iCounter As Integer

Also:

End Sub]

There's extra bracket at the end of your code.

Apart from that looks ok. Actually I just tested it and it works as expected.

[Emma35]

Ah no i think that's just how it copied and pasted onto the webpage. I've attached the error message and the code window below

[cyanidem]

Ok, quick googling returned this:
When a Form's control is linked to a recordset, and that recordset is empty (e.g. a table with no records in it yet.) then any reference to that control's value will trigger the following error:
"Run-time error '2427': You entered an expression that has no value."
My guess is that code you put in tries to count checkboxes bound to recordset and possibly one of those is null (new record maybe?).
Now, I'm not sure if you want to count those checkboxes as well or only unbound ones. So I see two possible solutions:
1. to count only unbound checkboxes, change their Tag property to something unique (like "CountMe" or whatever) and then add another If in code to check if ctrl.Tag="CountMe" before checking its value.
2. to count checkboxes bound to recordset add some more conditions before checking value as well, like:

If Not IsEmpty(ctrl) And Not IsNull(ctrl) And Not hasNoValue(ctrl)

That should resolve this error I think, can't test it at the moment unfortunately.

[Emma35]

This is turning out to be trickier than i thought. So would it be better if they were all unbound ?
I'm not really sure how to integrate the extra code into what i already have (my knowledge of VB is non existent)

[cyanidem]

Try this:

Private Sub Form_Current()
Dim iCounter As Integer
Dim ctrl As Control
iCounter = 0
For Each ctrl In Form.Controls
    If Not IsEmpty(ctrl) And Not IsNull(ctrl) And Not hasNoValue(ctrl) Then
        If TypeName(ctrl) = "CheckBox" Then
            If ctrl.Value = True Then
                iCounter = iCounter + 1
            End If
        End If
    End If
Next ctrl
Me.txtCount.SetFocus
Me.txtCount.Value = Str(iCounter)
End Sub

It will just omit all checkboxes with no value (if that's what you want).

[Emma35]

I think we're nearly there...i'm getting the error below when i open the Form

[cyanidem]

Remove that part and in that line leave only:

If Not IsEmpty(ctrl) And Not IsNull(ctrl) Then

I don't know where did I get it from

[Emma35]

Ok did that but it's now giving the previous '2427' error with the same line highlighted in yellow