Combo box with 2 columns

[mcgooie]

i have a combo box reading in two fields from a table - id and name. Is it possible to have "autofill" pick up the name but when i make my selection, the id becomes selected.

i.e. if i have these:
ID NAME
1 Pete
2 Dave
3 Alan

and i type in "Dav" and press enter, the value in the combo box will be the id "2".

thanks

 

[le888]

Go to the combo box in the "length column" put 0cm;5cm.

Le

 

[wazz]

what happens if you have two Daves? or two Daves and a Davis? you might want a (separate) search form that will give you all possible results first.

following what le888 said: data from the "bound" column is the data that is "used". the column you "see" is the first column with a width > 0.

 

[KingRudeDog]

Following what wazz said (or further explaining it)...the ID is the important field, it is the relational aspect which links the table to other tables (at least if your db is built correctly). You sound a little new to this so...

In the properties menu for that combobox:

Your ROW SOURCE will look something like this:
SELECT person.personID, person.person_name FROM Person ORDER BY [person_name];

You actually want TWO columns in the ComboBox, one for the id (which you already see when you use it, and one for the name)

as WAZZ mentioned, right now the bound column is the ID column, which is good...but you want that invisible so to speak.

So again in the properties menu for that combobox set your column widths to: 0";1"

This will hide the ID column (0")and show the name column (1").

 

[ShadowMan]

In order to get around duplicate items you'll need to expand your selection, perhaps by including the First and Last Name or concat 2 fields together - egave A and Dave B - that way you'll be able to differenciate the 2 entries, and with the ID being the Bound Column, the lookup will return the correct result.

 

[KingRudeDog]

wow...good idea Shadowman...