counting checkboxes 1

[mrwendell]

oh i am really feeling like an idiot...i have a access2000 form that contains checkboxes (10) per category (4). checkboxes were named check1to10 in the first category, check11 to 20 and so on... i want to count just the "yes" boxes for each category. because each category needs to be scored separately. i tried to put the following code on a command button...also on afterupdate...nothing! is there anyone who can help me get my dignity back!

Dim counta As Integer
For i = 1 to (10)
If Me("check" & i) = true Then counta = counta + 1
Next i
Me![subcomp1] = counta

 

[CajunCenturion]

On way is to assign the name of the checkbox into a variable, and then use that as a key into the form's controls collection and then evaluate the local control. Something like the following:

Private Sub cmdTally_Click()

   Dim TheCheckBox As Control
   Dim Idx As Integer
   Dim CheckBoxID As String
   Dim UnAssigned As Integer
   Dim NumChecked As Integer
   Dim NumUnChecked As Integer
   
   UnAssigned = 0
   NumChecked = 0
   NumUnChecked = 0
   For Idx = 1 To 10
      CheckBoxID = "chkCheck" & Trim(Idx)
      Set TheCheckBox = Me.Controls(CheckBoxID)
      If (IsNull(TheCheckBox)) Then
         UnAssigned = UnAssigned + 1
      Else
         If (TheCheckBox = 0) Then
            NumUnChecked = NumUnChecked + 1
         Else
            NumChecked = NumChecked + 1
         End If
      End If
   Next Idx
   
   Set TheCheckBox = Nothing
   
   MsgBox "Unassigned = " & Trim(UnAssigned) & vbCrLf & _
          "UnChecked = " & Trim(NumUnChecked) & vbCrLf & _
          "Checked = " & Trim(NumChecked)
          
End Sub

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[danvlas]

for i=0 to 3
for j=1 to 10
Me("subcomp" & i+1) = Nz(Me("subcomp" & i*10 + j),0) - Me("check" & i)
next
next

Check boxes can have a value of 0 (true) or -1 (false), so counting the True ones is the same as summing all of them and reversing the sign. i did this by substracting the values instead of summing them

the i loop counts the groups
the j loop counts the checkboxes within each group

Good luck



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[danvlas]

Sorry...correction...


for i=0 to 3
for j=1 to 10
Me("subcomp" & i+1) = Nz(Me("subcomp" & i+1),0) - Me("check" & i*10 + j)
next
next



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[TonyJollans]

Hi Dan,

Doesn't your code have the same problem which mrwendell's does - the answer to which is hidden in CajunCenturion's fine code? To reference a control you need to say Me.Controls("check" & i).

Enjoy,
Tony

 

[danvlas]

Tony: why? the controls collection is the default one...
Me.Controls("Textbox&quot
has absolutely the same effect as
Me("Textbox&quot
Try it...

I'm enjoying...




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[TonyJollans]

Hi Dan,

I shouldn't be so hasty and I don't need to try it, I know you're not wrong

I should be doing something else on a Sunday afternoon, but what is the problem with mrwendell's code? And has anything posted solved it? Checking for null is obviously a good idea but if he is getting nothing (as opposed to an error) that won't help him.

Keep on enjoying,

Tony

 

[danvlas]

counta = counta + 1

If initial value of counta is Null, then the result will also be null

His code actually works...but always returns Null.
Setting a default value to 0 would solve it, however I thought I had a better way to do it...






Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[TonyJollans]

Hi Dan,

But ..

Dim counta as Integer

.. means the initial value is zero.

The only thing I can see wrong with his code is that if any of the checkboxes is null he will get an 'invalid use of null' error, but that is not nothing.

I've been wrong many times and there may be something very obvious that I'm missing but I can't see how to help without more information.

Enjoy,
Tony

 

[danvlas]

Well spotted Tony...right, I should have read everything more carefully before jumping in...

Then it may be a display thing...control is calculated but the value is not displayed...it happened to me a few times.

Usually a Me.Repaint solves it.






Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[TonyJollans]

Hi Dan,

Agreed

It depends on what other code he may have before or after the posted code. I've often stared at code trying to find an error when the real error is that it isn't being run, or something afterwards is changing the results.

Enjoy,
Tony

 

[mrwendell]

Guys...I really enjoyed your banter. Unfortunately meetings pulled me away before I could try any of your suggestions. I do intend to try both versions and hopefully come up with a hybird...anyway thanks, and I am sure I will be back!!

 

[mrwendell]

CanjunCenturion...or anyone.....I am trying to run your code as above response suggest....
heres what I have done...my checkboxes are named check1(),check2(),check2()...so on to check10(). They are bound to a table but they are named check1,check2..(without the ())...pasted your code to a cmd btn


Dim TheCheckBox As Control
Dim Idx As Integer
Dim CheckBoxID As String
Dim UnAssigned As Integer
Dim NumChecked As Integer
Dim NumUnChecked As Integer

UnAssigned = 0
NumChecked = 0
NumUnChecked = 0
For Idx = 1 To 10
CheckBoxID = "Check" & Trim(Idx)
Set TheCheckBox = Me.Controls(CheckBoxID)
If (IsNull(TheCheckBox)) Then
UnAssigned = UnAssigned + 1
Else
If (TheCheckBox = 0) Then
NumUnChecked = NumUnChecked + 1
Else
NumChecked = NumChecked + 1
End If
End If
Next Idx

Set TheCheckBox = Nothing>>>>
I get an err at --set the checkbox = me.controls(checkboxid)
if I place my mouse over the (checkboxid) I get checkboxid = 1 but over thecheckbox I get thecheckbox = nothing.

can you please give me any insight/help as to what I am doing wrong. Should I take the () from the name and just use check#....I would REALLY appreciate your help!

 

[TonyJollans]

Hi mrwendell,

It's rather unusual to have names like check1() but they are allowed. I would remove the "()" but if you want to keep it then you must include it in building up he name in your code, so change the the first line in the For loop to ..

CheckBoxID = "Check" & Trim(Idx) & "()"

Enjoy,
Tony