Combo Box using RecordsetClone

[EddieN1]

I have a continuous form with the list of records filtered based on the Operator's selection controls. When the Operator selects the desired filters, I reconstruct the sql code for the RecordSet and update Me.RecordSet. I want another combo box to limit the listed rows to the filtered RecordSet... perhaps using a RecordsetClone property?

Thanks, Eddie

[June7]

No, not with RecordsetClone.

What you describe is a conditional or cascading combobox. Set the combobobx RowSource SQL with same filter criteria applied to form.

[Micron]

Sounds more like a search form to me (maybe with cascading combos, maybe not) but the form recordset needs to be a sql statement that makes use of the selected combo items and/or textboxes. Perhaps create the sql using something like a commandbutton click event and set the form recordsource property to it.

[Bulzie]

Seems like you just need to add that 2nd combo box to the sql line filter for RowSource? Probably do a IF/Then like:

If ComboBox1 <> null then

If Combobox 2 <> null then

subform.RowSource = Select * From YourTable Where field1 = Combobox1 and field2 = Combobox2

Else

subform.RowSource = Select * From YourTable Where field1 = Combobox1

End If

Else

If Combobox 2 <> null then

subform.RowSource = Select * From YourTable Where field2 = Combobox2

End If

End If

[EddieN1]

Hey guys, thanks for all the input, however, I have come up with a solution that other might use. When the routine that builds the sql for Me.RecordSource, I save the part of the sql that contains the Tables (FROM xxx INNER JOIN yyy, etc.) and the WHERE/AND clauses. Then in the Selection Combo boxes Enter Events, I update the field's RowSource with a custom SELECT DISTINCT clause using the same field names that are in the form's RecordSource, followed by the saved "FROM xxx INNER JOIN yyy WHERE zzz AND AAA, etc." sql string and then the custom ORDER BY fields. Works like a charm. Thanks again.

[CJ_London]

Your description is very confusing - suggest show some example data and the outcome required