Count "selected" check boxes on continuous form.

[Guest]

Access 2003. Continuous form with a check box (Yes/No) for each record.
Default value for check box is no (unchecked). User selects record by
clicking on check box. Form footer set up to display total number of records.
Using =Count(*) for that number, and it works fine. Also want to display
counter for records chosen (as in check boxes checked) Should be dynamic as
in that it increases or decreases as check boxes are checked or un-checked.
Can't get "DCount" to do it, but I think it's right weapon with limited skill
user. A little guidance please .................Thanks

 

[PC Datasheet]

A "checked" checkbox has the value of -1. So in the footer in the control
source of a textbox, use the expression:
=Sum(Abs([NameOfCheckBox]))
Abs returns the absolute value so it converts -1 to 1. Summing the
checkboxes then gives you the numer checked Yes.

 

[Guest]

Access 2003. Continuous form with a check box (Yes/No) for each record.
Default value for check box is no (unchecked). User selects record by
clicking on check box. Form footer set up to display total number of records.
Using =Count(*) for that number, and it works fine. Also want to display
counter for records chosen (as in check boxes checked) Should be dynamic as
in that it increases or decreases as check boxes are checked or un-checked.
Can't get "DCount" to do it, but I think it's right weapon with limited skill
user. A little guidance please .................Thanks

 

[Guest]

Was a little premature giving the [Yes] response. Answer looked clear and
straight forward, however not working for me. "Error" is displaying for both
counters when form opened, even before any boxes are checked. Tried checking
a few boxes for results, but no effect. Just to show what I've done, if I
have followed correctly:
control on form for check boxes called chkTempSelect
control on footer called txtCountSelections

In the Control Source of txtCountSelections I have place the code
=Sum(Abs([chkTempSelect]))

The results as noted above. Don't see how I could be doing this wrong.

A "checked" checkbox has the value of -1. So in the footer in the control
source of a textbox, use the expression:
=Sum(Abs([NameOfCheckBox]))
Abs returns the absolute value so it converts -1 to 1. Summing the
checkboxes then gives you the numer checked Yes.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Access 2003. Continuous form with a check box (Yes/No) for each record.
Default value for check box is no (unchecked). User selects record by
clicking on check box. Form footer set up to display total number of records.
Using =Count(*) for that number, and it works fine. Also want to display
counter for records chosen (as in check boxes checked) Should be dynamic as
in that it increases or decreases as check boxes are checked or un-checked.
Can't get "DCount" to do it, but I think it's right weapon with limited skill
user. A little guidance please .................Thanks

 

[Marshall Barton]

Access 2003. Continuous form with a check box (Yes/No) for each record.
Default value for check box is no (unchecked). User selects record by
clicking on check box. Form footer set up to display total number of records.
Using =Count(*) for that number, and it works fine. Also want to display
counter for records chosen (as in check boxes checked) Should be dynamic as
in that it increases or decreases as check boxes are checked or un-checked.
Can't get "DCount" to do it, but I think it's right weapon with limited skill
user.

Use the ecpression:

=Abs(Sum(field))

where field is the name of the **field** in the form's
record source table/query that the check box control has in
its ControlSource property. You can not use a control name
in an aggregate function.

 

[Guest]

Thanks Marsh, but I'm not there yet!
I think I will have to add a little more information for you. I am using the
query make-table function to create a temporary table for intermediate
processing. The query underlying my continuous form, queries that temporary
table alone ( No JOINS involved) and no Expressions in the temp table or
query. When I check some check boxes on my continuous form then go and view
the temp table in data sheet view, the selections have been correctly
recorded (-1 showing). The control to display the "selected" counter remains
as 0 (zero), and does not change.
Temp Table Name: tblTempTable
check box field name on tblTempTable: TempSelect
continuous form name: frmUserSelections
check box control on continuous form name: chkTempSelect
control on footer of continuous form name: txtCountSelections

Applying your guidance, I have:
Control Source field of chkTempSelect: TempSelect
Control Source field of txtCountSelections:
=Abs(Sum(tblTempTable.TempSelect))

Where have I failed? and my appreciation continues......................

 

[Marshall Barton]

Thanks Marsh, but I'm not there yet!
I think I will have to add a little more information for you. I am using the
query make-table function to create a temporary table for intermediate
processing. The query underlying my continuous form, queries that temporary
table alone ( No JOINS involved) and no Expressions in the temp table or
query. When I check some check boxes on my continuous form then go and view
the temp table in data sheet view, the selections have been correctly
recorded (-1 showing). The control to display the "selected" counter remains
as 0 (zero), and does not change.
Temp Table Name: tblTempTable
check box field name on tblTempTable: TempSelect
continuous form name: frmUserSelections
check box control on continuous form name: chkTempSelect
control on footer of continuous form name: txtCountSelections

Applying your guidance, I have:
Control Source field of chkTempSelect: TempSelect
Control Source field of txtCountSelections:
=Abs(Sum(tblTempTable.TempSelect))

Use the check box control's AfterUpdate event to recalculate
values on the form:

Me.Recalc