Count Checkboxes 2

[ghloid]

Hello all!

I have a fairly easy (as always, so I THINK it's easy anyway) question. I have a form with about 20 checkboxes on the form. There is also a text box at the top of the form which is labeled TOTAL. The table that the form is bound to has a column for each check box response (yes/no type fields), along with an ID number for each record, and a total field to store the total value. What I want on the form is when a user clicks a check box on that form (registering a YES value in the table then), I would like the total value in the Total textbox to update. So, from all 20 checkboxes on the form, if a user clicks YES to 15 of those, it will register a 15 in the total text box at the top (and of course in the table since it's tied to that field). Of course if a user unchecks a box then, it should deduct one from the total.

Seems like this is a simple running count kind of thing, but I'm not sure how to go about doing it. It would probably be best if there were some code on each checkbox that updated the Total value after each click. I'm just in a real brain dead state today, and I can't seem to think of anything.

If anyone out there knows any way to do this, please let me know.

THANKS!

 

[jimb0ne]

the way I'd go about it is to name the textboxes the same, but have numbers for each, for example:

Check1
Check2
Check3
Check4, etc.

if you have 20, you could just create a sub that runs everytime someone clicks a checkbox, it would look like this.

Private Sub CountChecks()
dim iCounter as integer
dim tempCtrl as control
dim iTotal as integer

for each tempCtrl in me.controls
if tempCtrl.Type = acCheckBox then
for iCounter = 1 to 20
if tempCtrl.Name = "Check" & icounter then
iTotal = iTotal + 1
end if
next iCounter
end if
next tempCtrl

txtCheckTotal.text = iTotal
end sub

that should do it.

 

[rdodge]

What I was thinking to make the processing time not as intensive, is to use the following:

Global Variable

CurrentCheckboxValue as Long

Private Sub Checkbox1_BeforeUpdate(Cancel as Integer)
CurrentCheckboxValue = Checkbox1.Value
. . .
End Sub

Private Sub Checkbox1_AfterUpdate()
CheckboxTotal Checkbox1
End Sub

Sub CheckboxTotal(Checkbox As Object)
If Me.Checkbox.Value <> CurrentCheckboxValue Then
If Me.Checkbox.Value = -1 Then
Me.Textbox.Value = CStr(CLng(Me.Textbox.Value)+1)
Else
Me.Textbox.Value = CStr(CLng(Me.Textbox.Value)-1)
End If
End If
End Sub

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[ghloid]

Hmm, both nice responses there. I'm trying Jimb's idea first, and I keep getting an error from it. I put your code in the afterupdate event of the AfterUpdate event of the checkboxes. I renamed according to my scheme, so here's what I have:

Private Sub chkPoint1_AfterUpdate()
Dim iCounter As Integer
Dim tempCtrl As Control
Dim iTotal As Integer

For Each tempCtrl In Me.Controls
If tempCtrl.Type = acCheckBox Then
For iCounter = 1 To 20
If tempCtrl.Name = &quot;chkPoint&quot; & iCounter Then
iTotal = iTotal + 1
End If
Next iCounter
End If
Next tempCtrl

txtTotal.Text = iTotal

End Sub

My problem is, it keeps erroring out on the &quot;if TempCtrl.type = acCheckbox then&quot; line with an &quot;Object doesn't support this property or method&quot; statement. Am I missing a reference file somewhere? All of the checkboxes on my form are named in the convention &quot;chkPointx&quot; where x is a number from 1 to 20. The textbox is named &quot;txtTotal&quot; and I think I made all appropriate changes in the code. Anyway, it never really gets to the lines that deal with the control names, so I don't think the problem is there.

I'm not exactly sure what I'm doing wrong here, but like I said, my brain is really slowing me down today (d'oh), so it could be something very simple.

If you can offer any more help, I'd appreciate it.

Thanks!

 

[jimb0ne]

remove the as control part of the dimension. so it would just be

dim tempCtrl

that should work I think.

 

[jimb0ne]

nm, it doesn't gimme a sec, I'll figure it out, been awhile since I did this.

 

[jimb0ne]

use the original code, except instead of tempCtrl.type use tempCtrl.ControlType, my bad.

 

[ghloid]

Well, I think we're on the right track now (I think). I modified the code as such:

Private Sub chkPoint1_AfterUpdate()
Dim iCounter As Integer
Dim tempCtrl As Control
Dim iTotal As Integer

For Each tempCtrl In Me.Controls
If tempCtrl.ControlType = acCheckBox Then
For iCounter = 1 To 4
If tempCtrl.Name = &quot;chkPoint&quot; & iCounter Then
iTotal = iTotal + 1
End If
Next iCounter
End If
Next tempCtrl

txtTotal.Value = iTotal

End Sub

I used the txtTotal.Value instead of the txtTotal.Text which seems to drive the function. In my example too, I only have 4 checkboxes (only put 4 on the form so far). So, when I click the checkbox, the total goes to 4. If I then uncheck the box, it doesn't seem to do anything. Looks like it's always looping through and putting in the number of checkboxes that are on the form (I would imagine it would put 20 down when all 20 boxes are on the form).

Still, my brain is confounding me. Am I missing something here?

By the way, thanks so much for your help. I think we're close, but just something small is missing.

Let me know if you have any more ideas.

Thanks

 

[jimb0ne]

again, my fault. change

if tempctrl.name = &quot;chkPoint&quot; & icounter then
itotal = itotal + 1
end if

to

if tempctrl.name = &quot;chkPoint&quot; & icounter then
if tempctrl.value = 1 then
itotal = itotal + 1
end if
end if

just have to check if the box is checked. Also, put the code on the click event, not the afterupdate event.

 

[rdodge]

Private Sub chkPoint1_AfterUpdate()
Dim iCounter As Integer
Dim tempCtrl As Control
Dim iTotal As Integer

For Each tempCtrl In Me.Controls
If tempCtrl.ControlType = acCheckBox Then
For iCounter = 1 To 4
If tempCtrl.Name = &quot;chkPoint&quot; & iCounter Then
If tempCtrl.Value = -1 Then
iTotal = iTotal + 1
End If
Exit For
End If
Next iCounter
End If
Next tempCtrl

txtTotal.Value = iTotal

End Sub


Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[rdodge]

Jim, if the checkbox is checkmarked, it has a value of -1 (or True) and if it's not checkmarked, then it has a value of 0 (or False).

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[jimb0ne]

again, my bad.

 

[ghloid]

You guys are the BEST!!!! Thanks for the help Ron. It works like a charm now! I put the code on the other boxes on the form, and it's churning away like clockwork.

I gave both of you stars cause you both were on the right track.

Thanks again!