Criteria For Subform Combobox Rowsource

[Tom]

I have the following tables:
TblState
StateID
State

TblCounty
CountyID
StateID
County

TblProjectState
ProjectStateID
ProjectID
StateID

TblProjectCounty
ProjectCountyID
ProjectStateID
CountyID

A project may be in multiple states and multiple counties in a state. I have
a main form based on TblProjectState and a subform based on
TblProjectCounty. There is a combobox in the main form with a row source of
TblState for entering StateID. How can I set up the rowsource for the
combobox in the subform for selecting counties? That combobox should only
list the counties in the State selected in the main form. I tried using a
query based on TblCounty and setting the criteria to
Forms!MyMainForm!StateID but the subform loads before the main form and
consequently StateID in the main form is not available to the criteria in
the query and a parameter dialog box comes up asking for
Forms!MyMainForm!StateID.

Thanks for all help!

Tom

 

[RuralGuy]

Leave the RowSource of the ComboBox on the SubForm set to "" until the
AfterUpdate event of the ComboBox on the main form. Then:

Me!SubFormControlName.Form!ComboBoxName.RowSource = YourQuery (or
"Your SQL")

Replacing my dummy names with your real names of the SubFormControl
and the ConboBox.

I have the following tables:
TblState
StateID
State

TblCounty
CountyID
StateID
County

TblProjectState
ProjectStateID
ProjectID
StateID

TblProjectCounty
ProjectCountyID
ProjectStateID
CountyID

A project may be in multiple states and multiple counties in a state. I have
a main form based on TblProjectState and a subform based on
TblProjectCounty. There is a combobox in the main form with a row source of
TblState for entering StateID. How can I set up the rowsource for the
combobox in the subform for selecting counties? That combobox should only
list the counties in the State selected in the main form. I tried using a
query based on TblCounty and setting the criteria to
Forms!MyMainForm!StateID but the subform loads before the main form and
consequently StateID in the main form is not available to the criteria in
the query and a parameter dialog box comes up asking for
Forms!MyMainForm!StateID.

Thanks for all help!

Tom

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[Tom]

How will the combobox display any records previously entered when the form
opens if the combobox has no Row Source when the form opens?