Clearing bound checkboxes on a subform in access

Question

i have a form with a subform query on it. The subform query has a row of checkboxes (bound to the table) for users to select multiple items. When the users press a command button another query is run to search for all checkboxes that are marked true and then open another form. I am trying to make it so that when another command button is pressed, all of the checkbox values on the subform query change to false. So far the code that i am using only changes the last selected checkbox to false. How do I get it to change all of the values to false?

Dim ctl As Control

For Each ctl In Me.SuppliesQuerySubform.Controls
    If ctl.ControlType = acCheckBox Then
        If ctl.Value <> False Then
            ctl.Value = False
        End If
    End If
Next ctl

E Mett E Mett · Accepted Answer · 2015-10-18T13:09:18

You have two options:

1) Update the records using SQL. Something like UPDATE tblItems SET IsSelected = FALSE WHERE ... (Replace tblItems with your table and IsSelected with the field the checkbox is bound to.) Then you should refresh your subform with .Requery

2) Use RecordsetClone. This is actually a copy of the records shown in your subform which you can loop through and update.

With Me.RecordSetClone
   .MoveFirst
   Do While .Eof = false
      .Edit
      .Fields("IsSelected") = FALSE
      .Update
      .MoveNext
   Loop
End With

Clearing bound checkboxes on a subform in access

1 Answers