Counting Check Boxes

[LarryDeLaruelle]

I have a form with a series of 32 check boxes labeled, more or less, as Item1, Item2 . . . Item32.<br><br>I want to tally the number of yes responses and display that number on the form.<br><br>Right now I am using 32 If, Then statements to increment the counter.&nbsp;&nbsp;What I would like to do is use a For, Next and reference the field name with a variable &quot;Item&quot; & LoopCounter, test the value and increment the counter.<br><br>I have not found a way to tell Access that the variable I have created is a Field Name and not an independent variable.<br><br>Has anyone done something like this?&nbsp;&nbsp;I could use any advice anyone may have.<br><br>Thanks. <p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br>

 

[DougP]

This is a more elegant solution than 30 some odd ifs<br>Basically you are looping thru every Object on the current form and seeing if it is a Checkbox. If it is a checkbox then see if it's checked or not. If it's checked count it.<br>Counter1 has the total of Checked, Checkboxes<br>-------------------------<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim ctl As Control, frm As Form<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim intI, Counter1 As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Counter1 = 0<br>&nbsp;&nbsp;&nbsp;&nbsp;Set frm = Forms(Screen.ActiveForm.FormName)<br>&nbsp;&nbsp;&nbsp;&nbsp;For Each ctl In frm.Controls<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;With ctl<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Select Case .ControlType<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case acCheckBox<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If .Value = True Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Counter1 = Counter1 + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End Select<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End With<br>&nbsp;&nbsp;&nbsp;&nbsp;Next<br>&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print Counter1<br>----------------------------<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[LarryDeLaruelle]

Thanks Doug, but, as usual, I may have left out a critical element to the problem.<br><br>The 32 check boxes are on a tabbed control and span two tabs.&nbsp;&nbsp;I also have a second set of 21 check boxes on a third tab which I also want to tally.<br><br>Will your code work in this type of situation or would it need to be modified to identify the individual tabs on the control?&nbsp;&nbsp;If so, how would I do that?<br><br>Sorry if I seem somewhat dense on this stuff; I've only been trying to write serious code in VBA for about six months and my knowledge level hasn't caught up with my ambitions.<br><br>Thanks again. <p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br>

 

[DougP]

I'm not sure, try it.<br>I don't use TAB's but it seems to me that it is just another item on the main form which in that case the code above WILL work.<br>Are your check boxes in a FRAME? that might make a difference.<br>Also see if the numbers are consecutive.<br>i.e. Check1, Check2 <br>Or did you re-name them <br><br>You'll just have to try it.<br>It if dose not work.... And you get an error, Write down the exact syntax and come back here with it or e-mail me direct.<br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[LarryDeLaruelle]

Thanks Doug:<br><br>I used your code and it worked fine except that it counts all check boxes on all tabs.<br><br>I modified the code to look at individual tabs (or at least I thought that's what it would do) but it still counts all check boxes on all tabs.&nbsp;&nbsp;Here's the code:&nbsp;&nbsp;&nbsp;&nbsp;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim ctl As Control<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim tbc As Control<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim pge As Page<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim intI As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Counter1 As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Counter1 = 0<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set tbc = TabCtl0<br>&nbsp;&nbsp;&nbsp;&nbsp;Set pge = tbc.Pages(1)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;For Each ctl In tbc.Controls<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;With ctl<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Select Case .ControlType<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case acCheckBox<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If .Value = True Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Counter1 = Counter1 + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End Select<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End With<br>&nbsp;&nbsp;&nbsp;&nbsp;Next<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print Counter1<br><br>I thought the Pages() was supposed to be a reference to the specific page (tab) but that doesn't appear to be the case.&nbsp;&nbsp;Any ideas on what I should try next?<br><br>By the way, what is the intI that you dim in the code you sent me?<br><br> <p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br>

 

[JimConrad]

In reference to your original question...<br><br><FONT FACE=monospace><br>Dim i&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As Integer<br>Dim countTrue As Integer<br><br>countTrue = 0<br><br>For i = 1 to 32<br>&nbsp;&nbsp;If Me(&quot;Item&quot; & i).Value = True Then&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;countTrue = countTrue + 1<br>&nbsp;&nbsp;End If<br>Next<br><br></font> <p>Jim Conrad<br><a href=mailto:jconrad3@visteon.com>jconrad3@visteon.com</a><br><a href= > </a><br>