Linking Combo Boxes?!?!?!?Arghhh!!?

[Ryath]

Hi all,

The problem i'm having at the moment is linking "categorizing" two combo boxes, so from 1 table i have a field which contains categories and another containing parts eg

Category Part
X 1
X 2
X 3
Y 4
Y 5
Y 6
Z 7
Z 8
Z 9

I've got it working one way.... so i select the category and it automatically limits the list of Parts according to category..... using

Source Data Category Combo:-
SELECT Distinct [Category] FROM [TBL blah] ORDER BY [Category]

AfterUpdate Category Combo:-
Private Sub Category_AfterUpdate()
Me.Part1.Requery
End Sub

Source Data Part Combo:-
SELECT Distinct [Part] FROM [TBL blah] WHERE [Category] = [forms]![FRM AllProducts].[Cat] ORDER BY [Part]

How can i get it working the other way?? so i have the full list avaliable of parts and when one is selected the appropriate Category is autmoatically selected (or limited to)... Thx all

 

[GBall]

I don't think you want to go there.
If you limit the categories to the one of the selected part, how will your user have the option of selecting by category again ?
You will need a button to reset the list.
If you want to just display the category of the selected part, you could just set category_combo.text to the category that the part belongs to.
However, and I haven't tested this, you may find that you trigger the afterupdate event of the category combo and force a requery of the part combo.
Just watch out for a possible endless loop in the after update events.
Another way is to have a text box that holds the category description and refresh that from both combos.
Maybe a design revisit will help.

Hope this helps. Regards,
Graham