Combo boxes dependent on each other

[sra2786]

I have two Combo boxes -- one for Material Number and one for Material Text. The user can select from only one Combo Box and the other Combo Box will be populated based on the selected value. If I select from the Material Text combo box, the correct value is displayed in Material Number ... but if I select from the Material Number combo box, no value is displayed in Material Text.

The RowSource is the same for both Combo Boxes
SELECT DISTINCT tblSubAssemblyInfo.ParentAssemblyID, tblPartMain.PartDescription
FROM tblSubAssemblyInfo INNER JOIN tblPartMain ON tblSubAssemblyInfo.ParentAssemblyID = tblPartMain.MfrPartNumber
WHERE (((tblSubAssemblyInfo.Plant)=[Forms]![frmExpandAssembly]![cboPlant]));

For Material Number, the Column Widths = 1";0"
For Material Text, the Column Widths = 0";1"



Private Sub cboMaterial_AfterUpdate()
Me.cboText = Me.cboMaterial.Column(1)
Me.cboText.Requery
End Sub


Private Sub cboText_AfterUpdate()
Me.cboMaterial = Me.cboText.Column(0)
Me.cboMaterial.Requery
End Sub

I am not sure what I am doing wrong.
Thanks.
Sandy

[Micron]

You could research 'cascading' combo boxes (because there's literally thousands of web pages on this) or try fixing your row sources for the combos (they are not bound, correct?).
Whichever is the 'child' gets filtered by a value chosen in the parent - they cannot be retrieving the same data (cannot have the same row source). If user drops down the child when there's no parent value chosen, there should be no list. The AfterUpdate event of the parent is used to requery the child so that any change to the parent resets the child.

It doesn't look to me like you have the correct sql for either box, but not sure without seeing the data. If cmb1 values are in one table and cmb2 values are in another table, there's no need to join them for either combo, based on my interpretation of your tables. Try creating select queries for each, where the child sql displays what you want according to the value chosen by the parent, then set each combo's row source to the applicable query or sql statement.