The VBA functions below can be used to count, and to clear, all of the selected check box controls on a Microsoft Access form. For example, a criteria form may present dozens of report options using check boxes, and each selected checkbox will open a report when a Preview button is clicked. You may wish to show a count of the reports selected, and also have a way to clear all of the selected check boxes at once. Or maybe you have a survey form with lots of check boxes and you would like to display how many boxes have been checked.
CountSelectedCheckBoxes() – This function passes back a count of selected check boxes on a form, or on a specific page of a tab control on a form.
ClearSelectedCheckBoxes() – This function clears the check boxes on a form, or on a specific page of a tab control on a form.
If you would like a total count for selected check boxes on a tab control page, as well as on the form itself, you will need to call CountSelectedCheckBoxes() for both cases and add the results together. To clear all of the check boxes on a tab control page, as well as on the form itself, call ClearSelectedCheckBoxes() for both cases.
basCheckBox
' basCheckBox Version 1.0.0
' Copyright © 2023 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected].
' Use (at your own risk) and modify freely as long as proper credit is given.
'
Public Function ClearSelectedCheckBoxes(frm As Form, Optional TabControlName, _
Optional TabPageName) As Boolean
' This procedure clears selected check boxes on the frm form.
' If there is a TabPageName, it is assumed there will be a TabControlName.
' If there is a TabPageName, it clears the selected checkboxes for that page.
' Otherwise, if there is a TabControlName, then it clears the selected
' checkboxes on that tab control.
' Otherwise, it clears all the selected check boxes on the form.
On Error GoTo Err_Handler
Dim Ctl As Control
Dim pag As Page
If Not IsMissing(TabPageName) Then
' Controls for a specific tab page.
For Each Ctl In frm.Controls(TabControlName).Pages(TabPageName).Controls
If Ctl.ControlType = acCheckBox Then
If IsNull(Ctl.Value) Or Ctl.Value = True Then
Ctl.Value = False
End If
End If
Next Ctl
ElseIf Not IsMissing(TabControlName) Then
' All controls on all pages of a tab control.
For Each pag In frm.Controls(TabControlName).Pages
For Each Ctl In pag.Controls
If Ctl.ControlType = acCheckBox Then
If IsNull(Ctl.Value) Or Ctl.Value = True Then
Ctl.Value = False
End If
End If
Next Ctl
Next
Else
' Controls on the form itself.
For Each Ctl In frm.Controls
If Ctl.ControlType = acCheckBox Then
If IsNull(Ctl.Value) Or Ctl.Value = True Then
Ctl.Value = False
End If
End If
Next Ctl
End If
DoEvents
ClearSelectedCheckBoxes = True
Exit_Proc:
On Error Resume Next
Set Ctl = Nothing
Set pag = Nothing
Exit Function
Err_Handler:
MsgBox Err.Number & " " & Err.Description, vbCritical, "ClearSelectedCheckBoxes()"
ClearSelectedCheckBoxes = False
Resume Exit_Proc
End Function
'
Public Function CountSelectedCheckBoxes(frm As Form, Optional TabControlName, _
Optional TabPageName)
' This procedure returns a count of selected check boxes on the frm form.
' If there are no selected check boxes it returns a 0.
' If there is a problem, it returns a Null.
' If there is a TabPageName, it is assumed there will be a TabControlName.
' If there is a TabPageName, it returns the count of selected checkboxes for that page.
' Otherwise, if there is a TabControlName, then it returns the count of selected
' checkboxes on that tab control.
' Otherwise, it returns a count of selected check boxes on the form.
On Error GoTo Err_Handler
Dim Ctl As Control
Dim pag As Page
Dim lngCount As Long
If Not IsMissing(TabPageName) Then
' Controls for a specific tab page.
lngCount = 0
For Each Ctl In frm.Controls(TabControlName).Pages(TabPageName).Controls
If Ctl.ControlType = acCheckBox Then
If Ctl.Value = True Then
lngCount = lngCount + 1
End If
End If
Next Ctl
ElseIf Not IsMissing(TabControlName) Then
' All controls on all pages of a tab control.
lngCount = 0
For Each pag In frm.Controls(TabControlName).Pages
For Each Ctl In pag.Controls
If Ctl.ControlType = acCheckBox Then
If Ctl.Value = True Then
lngCount = lngCount + 1
End If
End If
Next Ctl
Next
Else
' Controls on the form itself.
lngCount = 0
For Each Ctl In frm.Controls
If Ctl.ControlType = acCheckBox Then
If Ctl.Value = True Then
lngCount = lngCount + 1
End If
End If
Next Ctl
End If
CountSelectedCheckBoxes = lngCount
Exit_Proc:
On Error Resume Next
Set Ctl = Nothing
Set pag = Nothing
Exit Function
Err_Handler:
MsgBox Err.Number & " " & Err.Description, vbCritical, "CountSelectedCheckBoxes()"
CountSelectedCheckBoxes = Null
Resume Exit_Proc
End Function
Making Microsoft Access Work. Call (505) 369-1650.