2 Combo Box Menu from Multiple Query

Hey everyone,

I am having a hell of a time trying to set this menu system up. Here's
what I'm trying to do.

Combo Box One; is populated by names under properties "row source"
"Phase 1" through "Phase 10" (there are 10 Phases I want to sort from)

Once the phase has been selected a second combo box would populate.

So Combo Box 1 = Phase 3 ... Combo Box 2 = A list of people's names in
Phase 3.

I can not requery combo box 2 based on the selection of combo box 1
becuase of the complexity of the query to sort each phase. One table
source and multiple criteria must me met in the query to pass to the
next phase.

Is there a way to set the row source of combo box 2 to a specific query
based on a selection from combo box 1?

So Combo Box 1 = "Phase 1" sets row source of Combo Box 2 = QueryPhase1

I have no VBA experience macro would be the only automation solution
possible on my end.

Any help would be greatly appreciated.

Thanks in advance.

re: 2 Combo Box Menu from Multiple Query

Howzabout instead using a multi-level form/subform/sub-subform
structure where the master/child relationship is set on the relation
between the form's 'phase' selector combo and the subform's 'phase'
foreign key?

That way you won't need to requery, the valid cBox2 values would be
filtered based on the relationship.

Similarly, each row of the sub-subform people list would be linked to
the selected record in the subform.

King Ron of Chi

re: 2 Combo Box Menu from Multiple Query

I am not sure what you mean by:[color=blue]
> I can not requery combo box 2 based on the selection of combo box 1
> becuase of the complexity of the query to sort each phase. One table
> source and multiple criteria must me met in the query to pass to the
> next phase.[/color]

In order to tie the second combo box into the first combo box simply
refer to the first combo by its form field name (i.e. ME.cmbBox1) in
the source string for the second combo.

Here is a workable example (txtTelNo is the second combo, txtCarrier is
the first one):
Private Sub txtTelNo2_GotFocus()
Me.txtTelNo.RowSource = "SELECT First(tblNYUCircuitsServer.txtTelNo2)
AS [Carrier2 Telephone] FROM tblNYUCircuitsServer GROUP BY
tblNYUCircuitsServer.txtTelNo2 HAVING
(((tblNYUCircuitsServer.txtTelNo2) Is Not Null And Not
(tblNYUCircuitsServer.txtTelNo2)='') AND
((First(tblNYUCircuitsServer.txtCarrier2)) Is Not Null And
(First(tblNYUCircuitsServer.txtCarrier2)) LIKE '*" & _
me.txtCarrier & "*') AND
((First(tblNYUCircuitsServer.txtStatus))<>'Header' )) ORDER BY
First(tblNYUCircuitsServer.txtCarrier2),
First(tblNYUCircuitsServer.txtTelNo2);"
End Sub

The first combo box name/variable (i.e. Me.ComboBox1) must be on a
separate line surrounded by single quotes:
Me.ComboBox2.Rowsource = "SELECT Table.Field2 FROM Table WHERE
Table.Field1 = '" & _
Me.ComboBox1 & "';"

hmiller@hartford.edu wrote:[color=blue]
> Hey everyone,
>
> I am having a hell of a time trying to set this menu system up. Here's
> what I'm trying to do.
>
> Combo Box One; is populated by names under properties "row source"
> "Phase 1" through "Phase 10" (there are 10 Phases I want to sort from)
>
> Once the phase has been selected a second combo box would populate.
>
> So Combo Box 1 = Phase 3 ... Combo Box 2 = A list of people's names in
> Phase 3.
>
> I can not requery combo box 2 based on the selection of combo box 1
> becuase of the complexity of the query to sort each phase. One table
> source and multiple criteria must me met in the query to pass to the
> next phase.
>
> Is there a way to set the row source of combo box 2 to a specific query
> based on a selection from combo box 1?
>
> So Combo Box 1 = "Phase 1" sets row source of Combo Box 2 = QueryPhase1
>
> I have no VBA experience macro would be the only automation solution
> possible on my end.
>
> Any help would be greatly appreciated.
>
> Thanks in advance.[/color]

re: 2 Combo Box Menu from Multiple Query

Hey guys,

Thanks so far for the help except I don't understand VBA, sorry.

However I got it to work through a marco. The macro wasn't allowing me
to use Select, but form your code I noticed that Select needed to be in
"". So thanks for the help guys.

Happy Holidays