Subform Checkbox question

[Joe Williams]

I have a classic main form / subform setup. The subform has a field that is
a yes/no data type, represented by a checkbox in the field.

I would like to know how I can set up a control on the main form so that
when I click the control, it will toggle ALL of the checkboxes in the
subform on and off. Kind of like a "select/deselect all" thing that you see
in many windows applications.

Thanks

Joe

 

[Wayne Morgan]

Do you want to toggle all of them or set all of them to True or False?

Sample Code (untested):
Dim ctl As Control
For Each ctl In Me.NameOfSubformControl.Form.Controls
If ctl.ControlType = acCheckBox Then
ctl = True
'ctl = False
'Or to toggle
'ctl = Not ctl
End If
Next

You may also need to check to see if the control is enabled or hidden if you
don't leave them enabled and visible all of the time.

 

[Malcolm Cook]

I don't think Wayne's approach will work.

Rather, you need to procedurally edit the recordset underlying the form.

Here is a working example for a new command button I just placed on Orders
form of the Northwind example database.
It increments the Quantity of each product in the Orders_Subform on the
Order form.

Private Sub cmdtest1_Click()
Dim rs As DAO.Recordset
Set rs = Me.Orders_Subform.Form.Recordset
With rs
.MoveFirst
While Not .EOF
.Edit
!Quantity = !Quantity + 1
.Update
.MoveNext
Wend
.Requery
End With
End Sub

 

[Douglas J. Steele]

Malcolm: It's almost always preferable to use an Update query rather than
looping through a recordset like that.

As to Wayne's suggestion, as long as the controls are bound, updating them
should update the underlying data as well.