Edit all controls in an Access 2013 continuous form

Question

I have a button on my main form that marks all controls in a continuous subform as "Yes", however it only changes the first record in the continuous form. The second, third, etc. records will not change. I found an answer using DAO recordsets but the comboboxes I'm using are unbound.

This is the code I have. It modifies all comboboxes in the first record in the subform.

For Each ctl In Me![SubformName].Controls
    If ctl.ControlType = acComboBox Then
        If ctl.Name <> "Yes" Then
            ctl.Value = "Yes"
        End If
    End If
Next ctl

What do I change in my code to allow me to modify records past the first? Is there a way to directly refer to continuous form records?

Edited to add:

I still can't get it working for unbound comboboxes, but the following does work for bound ones.

Set rst = Me.SubformName.Form.RecordsetClone
rst.MoveFirst
Do While rst.EOF = False
    rst.Edit
    rst!FieldName = "Yes"
    rst.Update
rst.MoveNext
Loop

Are you really sure the comboboxes are unbound, i.e. have no ControlSource? Because unbound input controls in a continuous form don't really make sense. And then your code should actually change them all (because there is really only one instance of that control). — Andre
@Andre I'm using an unbound combobox to change the values in the bound comboboxes. — jjjjjjjjjjj
I can loop through all comboboxes using VBA in the unbound combobox. I haven't found a way to do this with DAO datasets that works with my form. — jjjjjjjjjjj
I'm sorry, I still don't understand. Maybe a screenshot with some annotations would help. You can use Subform.RecordsetClone to loop the records. — Andre
@Andre Thanks, another commenter mentioned RecordsetClone as well. I'm trying to get it to work. — jjjjjjjjjjj

Gustav Gustav · Accepted Answer · 2016-06-28T07:01:05

Open the RecordsetClone of the subform control.

Loop through this, and for each record set the value of the fields which are bound to a combobox to True.

Edit all controls in an Access 2013 continuous form

1 Answers