Count in Control Source

[goaksmith]

Related to thread

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=43238



I have put this formula into my control source.

Dcount("[Field Name]","table/query","[Field Name] = [Textbox/Combo]")

And it wasn't until after that I realized it wasn't exactly want I wanted. I would like a way to add different combo boxes within a record. For example, I have 10 combo boxes. And boxes 1-5 have yes and 6-10 have no I would like the field to return the number 5 because 5 fields have yes. If this changes to 1-6 have yes and 7-10 have no I would like this number to change to 6. Any help on this would be great. Thanks!

 

[WayneRyan]

goaksmith,

You can loop through them and check, but you can also:

NumTrue = (10 + (c1 + c2 ... c10)) / -2

Wayne

 

[goaksmith]

I am still fairly new to access and I am not really sure what you are telling me to do. It appears to be working with two of my control sources but not with the other two. And I did the exact same thing on both. The two that it is not working on have default values of Yes. Would that make difference?

 

[goaksmith]

The two boxes that are not working have a default value of Yes. And the Control source counting these as 2. There are a total of 30 boxes that are being added in one control source. Everytime I change a yes to no the number drops by 2. Why does this happen? Any help would be great. Thanks!

 

[WayneRyan]

goaksmith,

The 10 should have been replaced by a 30. But, this was
just an exercise in logic.

The real way to solve this is with a few lines of code.

What you want to do is loop through your controls on your
form and count the number of CheckBoxes that are true.

That is not too difficult.

I'll try to find or provide a sample.

Wayne

 

[WayneRyan]

goaksmith,

Some code, assign it to a command button, or whereever.

' **************************
Dim NumTrue As Integer
Dim ctl As Control

NumTrue = 0

For Each ctl In Forms!frmMain
If ctl.ControlType = acCheckBox Then
If ctl = -1 Then
NumTrue = NumTrue + 1
End If
End If
Next ctl
' **************************

hth,
Wayne

 

[goaksmith]

I know very little about coding. I am still a new user and try to avoid code at all cost. Can you explain a little bit what this is supposed to do and maybe a little more detail on how to do it? Sorry, thanks for the help though.

 

[WayneRyan]

goaksmith,

Basically, this code could go on the BeforeUpdate event
of your form. Get the form in design view. Right-Click
on the little black square in the upper-left corner.
Select properties. Click on the BeforeUpdate event.
Select [Event Function] in the middle column. Click
on the three dots on the right-hand side. Paste the
code in there.

What it will do is; when your form changes (clicking
on a CheckBox) the code will run.

The code loops through all of the controls of your
form. If it is a checkbox, and if it is checked,
it will add 1 to a counter (NumTrue).

If you made a new textbox on your form "NumTrue" and
it was unbound (No control source), you could put
a line at the end of the code:

Me.NumTrue = NumTrue

and it would display the count every time your form
changed.

Wayne

 

[goaksmith]

WayneRyan,

Ok, I added the code to the form. But the boxes aren't check boxes. They are text boxes with a list of either yes or no. And the default is to yes. I am assuming that would change the code that you gave me.

Also, in total there are 45 of these list boxes. I have 4 different text boxes that add together different groups of these list/text boxes. Would I then just add together the names of the fields that I wanted to add together after the code works correctly?

Thank you for all your patience. I really appreciate it.

 

[WayneRyan]

goaksmith,

If the names of the textboxes make sense then
something like this would work:

' ************************** 
Dim NumTrueA As Integer 
Dim NumTrueB As Integer
Dim NumTrueC As Integer
Dim NumTrueC As Integer

Dim ctl As Control 

NumTrueA = 0 
NumTrueB = 0 
NumTrueC = 0 
NumTrueD = 0 

For Each ctl In Forms!frmMain 
If ctl.ControlType = acTextBox Then 
   If Mid(ctl.Name, 1, 4) = "GrpA" And  ctl = -1 Then 
      NumTrueA = NumTrueA + 1 
   ElseIf Mid(ctl.Name, 1, 4) = "GrpB" And  ctl = -1 Then 
      NumTrueB = NumTrueB + 1
   ElseIf Mid(ctl.Name, 1, 4) = "GrpC" And  ctl = -1 Then 
      NumTrueC = NumTrueC + 1
   ElseIf Mid(ctl.Name, 1, 4) = "GrpD" And  ctl = -1 Then 
      NumTrueD = NumTrueD + 1 
End If 
Next ctl 
'
' Put counts into your screen textboxes
'
Me.txtGrpA = NumTrueA
Me.txtGrpB = NumTrueB
Me.txtGrpC = NumTrueC
Me.txtGrpD = NumTrueD
' **************************

hth,
Wayne