List Box not Working in Sub Form

[JD McLeod]

I have a cascading list box that works perfectly in my regular form. The
second list box is dependent on the value chosen in the first list box. When
I insert it into another form as a subform, I get parameter value errors. I
saw another post that leads me to believe that i can't use this. It said
that the subform wasn't really open, so my formula couldn't refer to the
subform. Is that so? If so, how can i get the cascading list boxes to work
in my subform?

 

[KenSheridan via AccessMonster.com]

My guess would be that in the control's RowSource property you are
referencing the form as a member of the Forms collection, e.g. Forms!MyForm!
MyControl when correlating one list box with another. Only parent forms are
members of the Forms collection, however, so a subform can't be referenced in
this way. You have two alternatives:

1. Reference the control via the Form property of the subform control of the
parent form:

Forms!MyParentForm!MySubformControl.Form!MyControl

2. A far simple solution, though, is to reference one control in the other's
RowSource property by the Form property alone:

Form!MyControl

This will work where both controls are in the same form, whether this is
being used as a parent form or subform because, in either case, the Form
property returns a reference to current instance of the form of whose
Controls collection each control is a member.

When requerying the second list box in the AfterUpdate of the first reference
the second by means of the Me keyword:

Me.MyListBox.Requery

The Me keyword refers to the current instance of the class in whose module
the code is being executed, so loosely speaking refers to the current form,
be it a parent form or subform.

Ken Sheridan
Stafford, England