Count Selected CheckBoxes On Continuous Subform

[PC User]

I have a continuous subform with a checkbox for each record. I am
trying to count the checkboxes that are checked on the form rather than
from the recordsource. The form is filtered; so the number of
checkboxes selected in the form and the recordsource are different.
I've started with some code, but I need help. This code is on the
AfterUpdate event of a checkbox in the continuous form.

Private Sub Selected_AfterUpdate()
Dim ctl As Control, frm As Form
Dim intCount As Integer
Set frm = Forms!frmMainEntry
intCount = 1
For Each ctl In Me.Controls
With ctl
Select Case .ControlType
Case acCheckBox
intCount = intCount + intCount
End Select
End With
Next ctl
Me.Parent.txtCountSelected = intCount
End Sub

Thanks,
PC

 

[Wayne Morgan]

I just tried the following code and it worked.

Dim rst As DAO.Recordset, rst2 As DAO.Recordset
Set rst = Me.RecordsetClone
rst.Filter = "Field1=True"
Set rst2 = rst.OpenRecordset
rst2.MoveLast
Debug.Print rst2.RecordCount
rst2.Close
Set rst2 = Nothing
Set rst = Nothing

 

[PC User]

Thanks for your reply. I tried to adapt your code to my situation and
it appears to count the selected checkboxes for the entire table. I
need it to count the selected checkboxes on the form, because table has
many items selected and the form is filtered by date range which
reduces the quantity of selected checkboxes for the results for which
I'm looking. How is your code referencing the continuous form?
==============================
Private Sub Selected_AfterUpdate()
Dim rst As DAO.Recordset, rst2 As DAO.Recordset
Set rst = Me.RecordsetClone
rst.Filter = "Selected=True"
Set rst2 = rst.OpenRecordset
rst2.MoveLast
Debug.Print rst2.RecordCount
rst2.Close
Me.Parent.txtCountSelected = rst2.RecordCount
Set rst2 = Nothing
Set rst = Nothing
End Sub
==============================
Thanks,
PC

 

[PC User]

I put your code into a public function, because I'll need to call it
from other events too. When I clear all the selections and then go
back to select a checkbox, I get an error on "rst2.MoveLast" which says
"Runtime error '3021' No current record." How can I resolve that?
=========================
Public Function SelectedCheckBox()
On Error GoTo Whoops
Dim rst As DAO.Recordset, rst2 As DAO.Recordset
Dim frm As Form
Set frm = Forms!frmMainEntry.Form.[fctlNotifications].Form
Set rst = frm.RecordsetClone
rst.Filter = "Selected=True"
Set rst2 = rst.OpenRecordset
rst2.MoveLast
Debug.Print rst2.RecordCount
frm.Parent.txtCountSelected = rst2.RecordCount
rst2.Close
Set rst2 = Nothing
Set rst = Nothing
OffRamp:
Exit Function
Whoops:
MsgBox "Error #" & Err & ": " & Err.Description
Resume OffRamp
End Function
=========================
Thanks for your help.
PC

 

[PC User]

Another question. How do I put the filter that I'm applying to the
subform into your code?

sfN.Filter = "[DueDate] Between #" &
[Forms]![frmMainEntry]![txtBeginningDate] & "# AND #" &
[Forms]![frmMainEntry]![txtEndingDate] & "#"
sfN.FilterOn = True

Thanks,
PC

 

[Wayne Morgan]

The code starts by getting a copy of the form's recordset, which you have
filtered by date range, so the records in the first recordset (rst) are only
those that you have in the form. Next, a filter is applied (true items only)
to this recordset (rst) and a new recordset is opened. The count of this
recordset would then be what is left after the form's filter and the True
(i.e. checked) filter have been applied.

Private Sub Selected_AfterUpdate()
Dim rst As DAO.Recordset, rst2 As DAO.Recordset
Set rst = Me.RecordsetClone
rst.Filter = "Selected=True"
Set rst2 = rst.OpenRecordset
rst2.MoveLast
Debug.Print rst2.RecordCount
rst2.Close
Me.Parent.txtCountSelected = rst2.RecordCount
Set rst2 = Nothing
Set rst = Nothing
End Sub

In the code you posted above, you are filling the textbox AFTER you've
closed the recordset. You need the statement to fill the textbox where I
have the Debug.Print statement.

In your second code posting, I see the above has been corrected. However,
there is a syntax error.

Set frm = Forms!frmMainEntry.Form.[fctlNotifications].Form

should be

Set frm = Forms!frmMainEntry.fctlNotifications.Form

Where fctlNotifications is the name of the subform control on frmMainEntry.
This may or may not be the same as the name of the subform itself.

I just tried the following on a filtered subform and it printed the correct
results in the Immediate window:

Dim frm As Form
Dim rst As DAO.Recordset, rst2 As DAO.Recordset
Set frm = Forms.form1.subform.Form
Set rst = frm.RecordsetClone
rst.MoveLast
Debug.Print "rst " & rst.RecordCount
rst.Filter = "Field2=2"
Set rst2 = rst.OpenRecordset
If rst2.EOF And rst2.BOF Then
Debug.Print "RecordCount = 0"
Else
rst2.MoveLast
Debug.Print rst2.RecordCount
End If
rst2.Close
Set rst2 = Nothing
Set rst = Nothing
Set frm = Nothing

The MoveLast will return a no current record if, once the filter(s) are
applied, there aren't any records to match the filter. The check added in
the If statement should fix this error. If there are no records, then the
recordset will be at both the beginning (BOF) and end (EOF) of the
recordset.

--
Wayne Morgan
MS Access MVP

Another question. How do I put the filter that I'm applying to the
subform into your code?

sfN.Filter = "[DueDate] Between #" &
[Forms]![frmMainEntry]![txtBeginningDate] & "# AND #" &
[Forms]![frmMainEntry]![txtEndingDate] & "#"
sfN.FilterOn = True

Thanks,
PC

 

[Wayne Morgan]

PS.

The rst.MoveLast is just to fill that recordset to get the Debug.Print after
it to give an accurate count for testing purposes. Neither it nor the
Debug.Print after it are needed. The count of rst2 is what you're after.

 

[PC User]

Thank you very much! I'll study your procedure and your explaination
more closely. What does the Debug.Print do? Is it suppose to printout
something? Is there a website where I can learn good debugging
techniques?

Thanks,
PC

 

[Wayne Morgan]

The Debug.Print outputs the results of the statement into the Immediate
window. In the default configuration of the VBA editor, this window is at
the bottom, right below the coding window. It is also the window you go to
if you use the Ctrl+G shortcut keys. If you have closed that window, you can
also open it by going to View|Immediate Window in the VBA editor.

The window can also be used for debugging by typing expressions directly
into the window. The "?" symbol, when used in the Immediate Windows, is
shorthand for Debug.Print, so if you entered the following in the window

?1+2

and pressed Enter, you would get 3 displayed in the window. You can also use
this window to change the values of variables in running code. If you put a
break point in the code and step through the code, if you get to a line that
has a variable whose value you want to change for testing purposes, you can
type variable = value (replacing these with the appropriate names and
values, of course) then continue to step through the code to see what the
different value does. To use variables in this way, they have to be "in
context", in other words, they have to be available to the code that is
running at the time.

In the View menu, you will see three other items below the Immediate Window,
the Locals Window, the Watch Window, and the Call Stack. These can also be
useful. The help file in the editor has explanations for these.