Counting Checkboxes On A Continuous Subform (1 Viewer)

PC User · Mar 25, 2005

I've read through a number of discussions in different forums on this topic, but there's something that I'm not getting to make my project work. So I'm attaching a sample to illustrate my problem. My goal is to count only those checkboxes that appear on the form to get the "total checkboxes" and count only those checkboxes that are checked (selected) on the form to get the "selected checkboxes count". This is because the next part of my project's development involves cycling through all shown selected records to send email, but I'm not there yet. The emphasis is to count what is on the form only, because what shows up on the form and what remains in the table are different.

Right now my design counts directly from the recordsource, which gives me an incorrect count. See attached sample to examine what I have so far and let me know if what I'm asking can be done. The problem comes when I add a date range and the count doesn't change.

Thanks,
PC

PC User · Mar 25, 2005

I've created a code that I think more closely takes information directly off of the form, but I still need someone's help. The Public Function CheckboxOnForm() give me an error. See attachment.

Thanks,
PC

RoyVidar · Mar 25, 2005

I haven't looked at the code, but you could try the following as the controlsource of your count text control

=abs(sum([NameOfYesNoField]))

PC User · Mar 25, 2005

I realized that in my next step in identifying the exact records that are selected are also going to be grabbed to put into an email module. This will send each selected record that contains project information to each individual staff member. Counts won't be enough, so I had to try something else. In order to check my new code, I'm sending the information to a report. When the code is corrected, I'll send the information to an email module. I'm trying to identify each record that is selected by a checkbox. See code below and attached file. I think that I should use SQL to build selection criteria in the "WHERE" statement while looping through the checkboxes on the form, but again I need help.
Code:
=====================================
Public Function RunReport()
On Error GoTo ErrorHandler
Dim MyDB As Database
Dim ctl As Control, frm As Form
Dim qdf As QueryDef
Dim i As Integer, strSQL As String
Dim strWhere As String, strIN As String
Dim flgAll As Boolean
Dim strReport As String

Set frm = Forms!frmMainEntry.fctlNotifications.Form
Set MyDB = CurrentDb()
strReport = "rptDisplaySelection"
strSQL = "tsubProgramList.ProgramID, tsubProgramList.* "
For Each ctl In frm.Controls
With ctl
Select Case .ControlType
Case acCheckBox
If frm.Selected.Value = True = True Then
strIN = strSQL & "'" & tsubProgramList.ProgramID & "=" & frm.tsubProgramList.ProgramID.Value & "',"
Else
End If
End Select
End With
Next ctl

'create the WHERE string, stripping off the last comma of the IN string
strWhere = " WHERE [ProgramID] in (" & Left(strIN, Len(strIN) - 1) & ")"

'if "All" was selected, don't add the WHERE condition
If Not flgAll Then
strSQL = strSQL & strWhere
End If

MyDB.QueryDefs.Delete "qsubNotificationsProgram"
Set qdf = MyDB.CreateQueryDef("qsubNotificationsProgram", strSQL)

DoCmd.OpenReport strReport, acPreview

DoCmd.Maximize
DoCmd.RunCommand acCmdFitToWindow
'RESET QUERY ****************
'strSQL = "SELECT * FROM tsubProgramList "
'MyDB.QueryDefs.Delete "qsubNotificationsProgram"
'Set qdf = MyDB.CreateQueryDef("qsubNotificationsProgram", strSQL)

ExitProcedure:
On Error Resume Next
Set rstClone = Nothing
Exit Function
ErrorHandler:
MsgBox Err.Description
Resume ExitProcedure
End Function
=====================================
If the number of checkboxes selected in the table were the same number of checkboxes shown selected on the form, then this wouldn't be a problem. Take a look at the table after you put the date range on the form and you'll see what I mean. Ultimately, I need to use only those records that are nessary to give notification, rather than all the selected record shown in the table. I've tried switching recordsources instead of applying a filter and the results came out the same. There must be some other way.

Thanks,
PC

RoyVidar · Mar 25, 2005

Are all your checkboxes on the same row? That's what you'll get when looping the controls. If they are on different rows (as in the checkbox is bound to a column in the underlying table), you'll need to loop the recordset.

Code:

dim rs as dao.recordset
set rs= frm.recordsetclone
rs.movefirst
do while not rs.eof
    if frm("Selected").value then
        strIn = strIn & rs.fields("ProgramID").value & ", "
    end if
    .rs.movenext
loop

- typed - not tested - and please, could you use code tag's for the code, it's difficult to read

PC User · Mar 25, 2005

Thank you for your reply Roy-Vidar. I'll test your code.

Thanks,
PC

PC User · Mar 27, 2005

I have a follow-up question Roy-Vidar. In order to printout all the records that have their checkbox selected, I've used your code in a function for looping through the form's checkbox controls. I can't seem to get it to work in generating a report. Could you take a look at my sample file that I've attached and let me know what I can do to make it work? Please look at Public Function RunReport()

Thanks,
PC

RoyVidar · Mar 27, 2005

Sorry, I'm a bit reluctant with regards to downloading. If you post the code, I'd be happy to take a look when I get time, else wait til someone else downloads.

For code to show indentations, like in my small sample, use code tags. To get

Code:

if somecondition then
    ' blah
end if

enclose the text with [.code.][./code.] tags, to make it easier to read (brackets are necessary, the dots, I put there just to show the tags, so don't use them). I wouldn't be surprised if one of the buttons in the reply pane included code tags, too, but my conservative settings won't allow them

PC User · Mar 28, 2005

I'm beginning to think that its easier to use just one recordsouce and use update queries to determine how the checkboxes should be checked. Two of my update queries are update parameter queries for the date range and I get an error when I use these two queries. The error is Run-time error '3061'
Too few parameters. Expected 2. How can I resolve this error?

Form Module Code:

Code:

Option Compare Database
Option Explicit

Private Sub btnDeselectAll_Click()
    On Error GoTo Whoops
    Dim db As DAO.Database
    Dim strSQL As String
    Dim frm As Form
    Dim sfN As Form 'Program Notification SubForm
    Set frm = Forms!frmMainEntry.Form
    Set sfN = frm.[fctlNotifications].Form
    Set db = CurrentDb()
        Call CheckNotificationDatesSelectNone
        strSQL = Me.storeSQL
        CurrentDb.Execute strSQL, dbFailOnError
        'DoCmd.RunSQL strSQL
    sfN.Refresh
    Form.Refresh
    Me.txtCountSelected.SetFocus
    Me.txtTotalRecords.SetFocus
OffRamp:
    Exit Sub
Whoops:
    MsgBox "Error #" & Err & ": " & Err.Description
    Resume OffRamp
End Sub

Private Sub btnSelectAll_Click()
    On Error GoTo Whoops
    Dim db As DAO.Database
    Dim strSQL As String
    Dim frm As Form
    Dim sfN As Form 'Program Notification SubForm
    Set frm = Forms!frmMainEntry.Form
    Set sfN = frm.[fctlNotifications].Form
    Set db = CurrentDb()
        Call CheckNotificationDatesSelectAll
        strSQL = Me.storeSQL
        CurrentDb.Execute strSQL, dbFailOnError
        'DoCmd.RunSQL strSQL
    sfN.Refresh
    Form.Refresh
    Me.txtCountSelected.SetFocus
    Me.txtTotalRecords.SetFocus
OffRamp:
    Exit Sub
Whoops:
    MsgBox "Error #" & Err & ": " & Err.Description
    Resume OffRamp
End Sub

Private Function CheckNotificationDatesSelectAll()
        Dim frm As Form
        Dim sfN As Form 'Program Notification SubForm
        Set frm = Forms!frmMainEntry.Form
        Set sfN = frm.[fctlNotifications].Form
    'Check to see that ending date is later than beginning date.
    If IsDate(txtBeginningDate) And IsDate(txtEndingDate) Then
        If txtEndingDate < txtBeginningDate Then
            MsgBox "The ending date must be later than the beginning date."
            Exit Function
        End If
            
            Me.storeSQL = "qupdNotificationSelectionYesDates"
    Else
        If IsNull(txtBeginningDate) And IsNull(txtEndingDate) Then
            'Set global variable
            Me.storeSQL = "qupdNotificationSelectionYes"
            End If
        End If

End Function

Private Function CheckNotificationDatesSelectNone()
        Dim frm As Form
        Dim sfN As Form 'Program Notification SubForm
        Set frm = Forms!frmMainEntry.Form
        Set sfN = frm.[fctlNotifications].Form
    'Check to see that ending date is later than beginning date.
    If IsDate(txtBeginningDate) And IsDate(txtEndingDate) Then
        If txtEndingDate < txtBeginningDate Then
            MsgBox "The ending date must be later than the beginning date."
            Exit Function
        End If
            'Set global variable
            Me.storeSQL = "qupdNotificationSelectionNoDates"
    Else
        If IsNull(txtBeginningDate) And IsNull(txtEndingDate) Then
            'Set global variable
            Me.storeSQL = "qupdNotificationSelectionNo"
            End If
        End If

End Function

One Of The Queries' SQL Code:

Code:

UPDATE tsubProgramList SET tsubProgramList.Selected = Yes
WHERE (((tsubProgramList.DueDate)>=[Forms]![frmMainEntry]![txtBeginningDate] Or (tsubProgramList.DueDate)<=[Forms]![frmMainEntry]![txtEndingDate]));

Thanks,
PC

RoyVidar · Mar 28, 2005

Executing stored queries is preferred by many vs using the form recordset. When the stored query is parameterized, you'll need to resolve the parameters, or as in the following sample, let Access resolve them.

Code:

dim qd as dao.querydef
dim prm as dao.parameter
set qd=db.querydefs(me.storeSQL)
for each prm in qd.parameters
    prm.value = eval(prm.name)
next prm
qd.execute
set prm=nothing
set qd=nothing

- typed not tested - and thank you, much easier to read the code now

PC User · Mar 31, 2005

Thanks for your help. For others interested in this solution, I'm posting the working sample that I've developed.

SheaLee · Aug 17, 2006

This is awesome...

Counting Checkboxes On A Continuous Subform (1 Viewer)

PC User

Registered User.

Attachments

PC User

Registered User.

Attachments

RoyVidar

Registered User.

PC User

Registered User.

Attachments

RoyVidar

Registered User.

PC User

Registered User.

PC User

Registered User.

Attachments

RoyVidar

Registered User.

PC User

Registered User.

RoyVidar

Registered User.

PC User

Registered User.

Attachments

SheaLee

Registered User.

Similar threads

Users who are viewing this thread