Cascading Combo Boxes Requery Problem

[Jo22]

I have a subform [Quote Query] which has several cascading combo boxes. [cmbProduct] will populate the appropriate list selection for [cmbPattern], which will populate the appropriate list selection for [cmbColor].

[cmbProduct] has an afterUpdate event - Me![cmbPattern].requery
[cmbPattern] has an afterUpdate event - Me![cmbColor].requery

Everything works fine for the first record, but when i go to the next record and change the product, [cmbPattern] goes blank for the first record. I the second record to requery but I want to keep the first record as entered. If I change the product in the second record I should get a new list to select from in [cmbPattern].

[RuralGuy]

Are your ComboBoxes "bound"? i.e. Do they have a ControlSource?

[Jo22]

Yes, they are bound.

[Jo22]

I should mention that the subform is in datasheet view, if that makes any difference.

As mentioned, I want to requery the current record only. How do I prevent the requery from erasing the data in the first record?

[RuralGuy]

A Requery renews an entire Recordset. Have you tried using a Refresh instead?

[Jo22]

Apparently I cannot refresh one record. I tried using Me.Form.Refresh but I just get a runtime error.

[RuralGuy]

Try Me.Refresh

[Jo22]

Unfortunately I get the same results with Me.Refresh - It clears out the data in the preceding record. It will not allow me to have different products in the recordset. It will allow me to change the product and the pattern if I go to a new form, but that does not work because each record set belongs to one order and I need to be able to order more that one product on the same order.

[Jo22]

AHA! I found the error. I finally thought to take a look at the subforms underlying query to see what's happening in there. I noticed that I wasn't losing the data at all. It just wasn't showing on my form. I knew then that the problem had to be related to the subcontrol names on my form.

and of course, that was the problem. Somewhere along the way I changed the control name slightly in the table but did not re-select it from the field list on the subform. Because the name change was so close, I did not notice it.

It's hard to believe how many hours I spent on this problem. A good lesson from the school of hard knocks I guess.

[RuralGuy]

Outstanding. Thanks for posting back with your success and solution.