Thanks ! That was really helpful when I know the name of what I am dealing with.
Now, New Probs is, I did what I want so far on Dropdown2. But on clicking dropdown2, there is a MsgBox up and Ask me to put value in. When I insert the table1.ID (what it suppose to be on dropdown1) it returns a value based on that ID number. eg. I choose Food in dropdown1 and when I click on dropdown2, the window appear and I put 1. The dropdown2 then list: Snack, Meat, Veggie. which is correct but I can also put 2 and dropdown2 return list of Toy Category. I did on new form on the same code, it work completely fine but when I use the same code on REAL form(which got Buttons/subforms and so many on...) the wondow appears...
anyone know what is the problem here? Well, this is my code
Private Sub Subseg1_AfterUpdate()
Me.Subseg2.RowSource = "SELECT Subsegment2.Subsegment2, Subsegment2.SEGID " & _
"FROM Subsegment2 " & _
"WHERE Subsegment2.SEGID = " & Nz(Me.Subseg1) & _
" ORDER BY Subsegment2.SEGID"
Pay attention to the fact that the text shown in a combo does not necessarily correspond to its Value property. The Value of a combo (if it's multi-column) depends of the BoundColumn property of the combo.
So, is Me.Subseg1 a multi-column combo? If the answer is yes, what's its BoundColumn property? In other words, what do you pass to Me.Subseg2.RowSource? Add a breakpoint in the procedure Subseg1_AfterUpdate and check whats the value of Me.Subseg1 (Me.Subseg1 = Me.Subseg1.Value), or check the value of Me.Subseg2.RowSource after changing it.
If you need to use the value in another column of a combo, use the syntax:
Where x is the index of the column, with the first (leftmost) column being Column(0).
Yes it was Multi-column and Me.Combo.Column(x) did just fine.
Just for my knowledge, Can I select the column without showing on the dropdown list? This is my dropdown1 look like so far:
Category | ID
Food | 1
Toy | 2
Other | 7
And so the dropdown2 and 3(with more record to choose from) My Combo.Value is 'Food' or Category field. But right now the list shows both Category and ID, I need to keep my ID so that I can use Me.Combo.Column() like you suggest, but does not want to show them on the list.
The Users are ok so far with the ID showing but, you know its not so beautiful
Thanks for the help out! Sinndho, You saved my day.