VBA to uncheck ALL checkboxes in a subform

[excellenthelp]

Hello,



What is the correct code to uncheck ALL checkboxes in a subform when an user clicks on "cancel" button on a main form?

Here is the code I have tried to use - to no success:

Private Sub btnReturn_Click()
   Dim ctl As Control
   For Each ctl In Me!frmSupport_CARptUpdate_Subform.Form
        If ctl.ControlType = acCheckBox Then
            ctl = False
        End If
    Next
    DoCmd.Close
End Sub

I suspect it is the reference error. Debug leads me to the red highlighted area.

Thanks!

[June7]

What do you mean by 'all' checkboxes - there are multiple checkbox controls as seen in Design view OR there is one control that is repeated for multiple records as seen in Form view?

Is frmSupport_CARptUpdate_Subform the name of subform container control? I always name container different from the object it holds, like: ctrDetails.

For Each ctl In Me.ctrDetails.Form

[excellenthelp]

There is one checkbox in the subform's design view.

In the form view, there is a field with the checkboxes. Some are checked and some are not.

As you said, there is one control that is repeated for multiple records as seen in Form view.

[June7]

I forgot to ask what 'no success' means - error message, wrong results, nothing happens? What is exact error message?

If checkbox is bound to field then your code would do no more than change the value of current record. You need to UPDATE all relevent records.

[excellenthelp]

No success = No change, except to first record on top of the datasheet (subform in main form in Form view)

[June7]

Possibly I edited my post as you were posting. Might read it again.

[excellenthelp]

Update? How do I update a subform?

[June7]

Options:

1. run an UPDATE sql action on the table, something like
CurrentDb.Execute "UPDATE tablename SET fieldname=False WHERE ID=" & Me.ID

2. cycle through records of subform - this is more complicated

[excellenthelp]

Okay thanks. I will update this thread with a solution when I find one.

Thanks, June7!

[excellenthelp]

HeH! It was an easy code! Duh me.

Private Sub btnReturn_Click()   Dim strSQL As String
        strSQL = "Update qryAIA_CARptUpdate Set [CARptUpdate] = False"
        CurrentDb.Execute strSQL


    DoCmd.Close
End Sub

Marking this as SOLVED.

Again, thank you, June7!