Counting Checked Checkboxes on a Subform in Datasheet View

[j.t.w]

I have a subform in datasheet view with 2 checkboxes for each record.
I want to check each record to see if at least one checkbox is set to
"true" (checked) and have it be counted.

I found and modified this code, that comes close, but not exactly.

intTotalChecked = 0
For Each ctl In Forms!frm_InvoiceStatus!subfrm_InvoiceStatus.Form
If ctl.ControlType = acCheckBox Then
If ctl.Value = True Then
intTotalChecked = intTotalChecked +1
End If
End If
Next
MsgBox = "Total Boxes Checked =" & intTotalChecked

Stepping through the code, it seems to loop through the controls and
checks the value of the checkboxes correctly, but only once and not
for all of the records that are showing. I think this would work for a
single form view. Is there a way to count the number of records with
checked checkboxes that are visibly shown on the current subform and
not based on a query?

I'm trying to let the users know how many records they have checked
before refreshing the main form, which will no longer show after
clicking on the "next" button.

Anyway, hope someone can help me. TIA.
j.t.w

 

[Graham Mandeno]

You could create a textbox in the footer of your subform. Set its
ControlSource to:
=Abs(Sum([Field1] or [Field2]))

This will keep a running count of the number of records that have one or
both checkbox fields checked.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

 

[j.t.w]

Dear Graham,

Thank you for replying so quickly.

Unfortunately, I could not get your suggestion to work. I put a
textbox into the subform's footer as you mentioned, but it doesn't
show because I'm using the datasheet view for my subform.

Am I missing something?

I am using Access 2000, if this matters.

I am using a tab control to track invoices. On the first tab, I show
all of the records(invoices) that are created. These records get
updated throughout the day. On the subform, the user checks a checkbox
to note which of the invoices will be scanned.

The user can then click on a button to update the subforms on the
other tabs. Basically, all of the forms(subforms) refresh and the
records show up on their respective tabs.

The second tab shows which invoices are marked with a check in the
scanned checkbox. The record source for the second subform is just a
query that determins if there is a check in the scanned checkbox.

Because there could be up to 200 invoices generated per day, and could
come back to the office at different times. I would like to show the
users how many invoices they are currently accounting for without
having to do a manual count by scrolling up and down the screen.

Anyway, thanks again, and sorry for being so long winded.

j.t.w

 

[Graham Mandeno]

Ah yes - I missed that you were using datasheet view.

Can you change your subform to a tabular continuous form instead? You can
make it look just like a datasheet, only with more functionality.

The other option is to place a textbox on your main form and set its
ControlSource to:
=[subform control name].Form![name of textbox]
where [name of textbox] is the textbox you created in the footer of the
subform.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.