Two column combo box not working as expected.

[Miles R]

Here we go again, something that should be simple does not work as expected and becomes increasingly frustrating.

I have a two column ComboBox populated at runtime using an sql query.
The result when I look at the values in the drop down list is of the form :-
AAAAA | 1234
AAAAA | 5678
AAAAA | 2345
BBBBB | 2356
BBBBB | 1234
CCCCC | 4567
CCCCC | 3456

When I select one of the rows, all that appears is AAAAA for example, not the second column.
What is worse, when I try to select the values using listindex, e.g. Me.ComboBox.ListIndex, I would expect the number to be the row selected -
e.g. a number from 0 to 6.
However it is not. The listindex values for each row are as follows :-


Row 0 ListIndex 0
Row 1 ListIndex 0
Row 2 ListIndex 0
Row 3 ListIndex 3
Row 4 ListIndex 3
Row 5 ListIndex 5
Row 6 ListIndex 5

Bizarre - it only seems to select the correct value when the first column changes.

I'm sure there is a simple explanation, but I can't find one yet.

[Minty]

First issue; Set the column count to 2, and make sure in the column widths you have two values 3cm;3cm.

Not sure what use the column index is in a combo box as only one value can be selected?
However what it is telling you is that the first distinct value for your first column is in rows 0,1,2 and your second distinct value is in rows 3 and 4 etc.

[Miles R]

Thanks Minty,
I do have column count of 2 and have column widths set.
Let me put this question another way. How do I get the values I thought I had selected? e.g. if I select row 4, I want to get the values BBBB 1234
I don't get those, I get value BBBB 2356, which is for row 3.
I am using Me.ComboBox.Column(0) and Me.ComboBox.Column(1) to get those values.

[Minty]

Interesting - The bound column is always available as simply Me.YourCombo
Any other values are available as you have written Me.YourCombo.Column(1)

Which event are you using to read these from - it should be the after update event, if you are using the OnChange Event you can get some weird results.

[Miles R]

You seem to have cracked it. I was using the OnChange Event and it was giving weird results. When I changed this to After Update it now works. The ListIndex also shows the correct value for the row selected (but I don't need that now).
Thanks again.