Combobox with two bound columns?

[lenni]

Hello everyone,

I've got a query that gives me something like a composite-key:

ID1.......ID2......txtDescription
1...........1........aaaaaa
1...........2........bbbbbb
1...........3........ccccccc
2...........1........dddddd
2...........2........eeeeee
etc.

Now, in a form's datasheet view, in which I've got records with all possible combinations of ID1 and ID2...
1.........3
1.........3
2.........1
1.........2
2.........1

... I'd like a locked combobox to display txtDescription for each record. Is this possible?
I hope my question is clear.
Thank you

 

[NauticalGent]

If you want the combo ox to display the txtDescription value, you will need to use change the bound colum to 3.

Edit: ignore this post and go to post #5 for the right information.

 

[bob fitz]

Why use a combo box. You could use a text box that uses DLookup() as its Control Source to return txtDescription

 

[lenni]

I'll try the textbox with de Dlookup control source, thanks!!!

 

[Pat Hartman]

The bound column of the combo is the column that gets SAVED. It is NOT the column that gets DISPLAYED. The column that gets displayed is the first, non-zero width column. So if you have a combo with three columns
RecID, LastName, FirstName

The Width setting will be
0,1,1

The bound column will be 1

When the combo is dropped, both last name and first name will be visible. When the combo is closed, only LastName will be visible.

If you want to show two columns in the one field, you can do that by changing the RowSource query to concatenate them. So instead of:
Select RecID, LastName, FirstName

You would have:

Select RecID, LastName & ", " & FirstName
From MyTable
Order By LastName & ", " & FirstName;

In this case, you would change the column count to 2. The bound field stays as 1, the widths property is 0,2

Combos WILL NOT WORK CORRECTLY when you have a multi-field unique identifier. They MUST have a single Unique ID. This was what converted me from using Natural keys to using Autonumbers.

 

[lenni]

Yes, Pat, thank you. I understand how combo boxes work, and how they don't work. Bob fitz's answer was the key to solving my problem. I already did it that way and works like a charm. Thanks to everyone!!!

 

[Pat Hartman]

Using a DLookup() works but it is less efficient than concatenating the string you want to see. The point being, why use two queries when one will do?

 

[lenni]

I do not want to concatenate any fields. That was really not my question.

 

[NauticalGent]

Pat, you are of course right. Re-reading what I wrote makes me wonder what I was thinking. Thanks for setting the record straight.