Move to row in combo box base on ID (hidden)

[bignose2]

Hi,

perhaps an odd way round but this is how I have done this for years but trying to tidy a few things so no fundamental changes

combobox : ID hidden 0;
id surname name
1 Jones/B bill
2 Jones/B baxter
3 Jones/B bonnie
4 Jones/C cleo
5 Jones/C charles

I type jones/b. It auto expands & selects the first Jones/b
I press enter and updates a table showing bookings with those surnames
I select no.3 Jones/b bonnie & press enter again, to choose ready to book.
it stores the ID OK using me.surname.Column(0)

However the combo box now starts the list still with no1 as = jones/b

I can press enter and proceed to book with the correct ID that was saved
so it all works but just looks wrong as not highlighting my selection

Basically is there a way to automatically move & highlight no.3 on the combo box. Can you select based on the hidden ID. You can set the .value but this is again for the ambiguous jones/b

I could change the source of the combo but would prefer not too so I can see those around BUT also I find as it matches & higlights no.1 it complicates things. Hard to explain & tried dozens of things

The list in 10,000 long so not even considered looping through and somehow matching with Me.surname.ItemData(x)

I don't think I have made this very clear.

 

[gemma-the-husky]

you do select based on the hidden ID. (you can select a different bound column), but the default is column (0) which is the first column. The value of the combo box is the ID.

You can assign the combobox with a statement such as mycombobox = 4

your problem is that the combo box shows just one column, which is the first non-hidden column from the query, corresponding to the row tagged by the ID. Hence you see just Jones/C and not (also) the forename corresponding to the selected row.


So try this
Add an extra column to the query, so the query becomes

id, surname & ", " & forename, surname, forename

then make the combo column widths 0, 0.01 ,3,2
The second column needs to be very small, but specifically not zero.

Now the drop-down will "show" columns 3 and 4 (it won't show column 1, and it will have a virtually hidden column(2))
However the closed combo box will show the full name of the selection from column 2. This is irrespective of the fact that the width of the drop down is a tiny value, The combo box display lineshows the first non-zero width column, which is the second column. List boxes do this as well.

It's slightly awkward describing this, as the second column is actually defined as column(1), as combo boxes are zero based. Once you've seen it working you won't have any more problems.

Sort the query however you want, but probably surname/ascending and forename ascending