How can I reference a subform record set in a combo rowsource quer

[Guest]

I'm trying to create a filter criteria form on a subform with unbound combos.
The first combo selected will set the subform filter, then the other combos
should reference the data in the subform so that multiiple combo selections
won't result in no records (I guess this is called "cascading combos" in
Access parlance). I've tried it by modifying the SQL statement in the combos
rowsource properties at runtime but this is complicated and feels like
"re-inventing the wheel" each time. It would be far easier if I could
reference the subform's recordset in VB code the same way a table of
design-time query is referenced and create on-the-fly queries on that. Can
any guru out there show me how to set a reference to it? Thanks in advance.

Dave

 

[Michel Walsh]

You can probably use the FORMS!formName syntax.

The exact syntax depends of where you are (in fact, where is your Combo box,
not you) and where is the control you want to reach.


Take a look at http://www.mvps.org/access/forms/frm0031.htm


and use the syntax in the RowSource property, such as:


MyComboBox.RowSource = "SELECT whatever FROM somewhere WHERE
someField=FORMS!formNameHere! ... "


and that line of code can go in the got focus subroutine handler for the got
focus event of the combo box.




Hoping it may help,
Vanderghast, Access MVP