0

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
Community
  • 1
  • 1
jjjjjjjjjjj
  • 417
  • 8
  • 28
  • 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 Jun 27 '16 at 22:15
  • @Andre I'm using an unbound combobox to change the values in the bound comboboxes. – jjjjjjjjjjj Jun 27 '16 at 23:07
  • 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 Jun 27 '16 at 23:09
  • 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 Jun 28 '16 at 05:56
  • @Andre Thanks, another commenter mentioned RecordsetClone as well. I'm trying to get it to work. – jjjjjjjjjjj Jun 28 '16 at 17:41
  • `ctl.Name <> "Combo10"` – Andre Jun 29 '16 at 05:21

1 Answers1

1

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.

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • How can I get the index of the subform record that currently has focus? I can loop through the subform with RecordsetClone, but the code I wrote in the question still only runs for the first record. Not sure if it's actually looping properly. – jjjjjjjjjjj Jun 28 '16 at 17:41
  • My code is `Set rst = Me.SubformName.Form.RecordsetClone Do While rst.EOF = False` then the code in the question, then `rst.MoveNext Loop` at the end. – jjjjjjjjjjj Jun 28 '16 at 19:26
  • Your loop is correct. But read again: Don't use the controls. Set the value of the _fields_ you wish to adjust. – Gustav Jun 29 '16 at 09:37
  • No matter if I use the loop or `Combo10.Value = "Yes"`, it loops the correct number of times, but only loops through the first record again and again. Do I have to use something like `rst.Combo10.Value` instead? – jjjjjjjjjjj Jun 29 '16 at 19:39
  • Please, forget the combos. Do set the field values: `rst!SomeField.Value = False`. – Gustav Jun 29 '16 at 20:46
  • I see. I thought a "field" was a reference to a value in a combobox? – jjjjjjjjjjj Jun 29 '16 at 22:37
  • I can't get it working for unbound comboboxes, but it does work for bound ones. The code is edited into my question. Thanks for your help. – jjjjjjjjjjj Jun 29 '16 at 22:58
  • An unbound combobox you only have to set once as it always will have the same value on all records. – Gustav Jun 30 '16 at 12:28