ComboBox to display all fields not only the link (1 Viewer)

[andy_25]

Solved: ComboBox to display all fields not only the link

Hi all,

I have got a ComboBox which helps the user select a person they are looking for. When it is clicked it shows their FirstName, LastName and JobRole.

However once they have made the selection it only shows the first value i.e. Andy

Is there a way of getting the ComboBox to show all 3 values once one has been chosen?

Thanks

 

[CBrighton]

Set the column count to 3 and set the column width property.

e.g. if you have a 3 column combobox which is 15cm wide you could set the width to 5cm;5cm;5cm for even column spacing.

The bound column will define what the combobox.value (and if it's a bound control, which of the column's data is saved) returns, but combobox.column can be used in VBA to get data from the other columns.

 

[MStef]

Look at "DemoComboB3A2000.mdb" (attachment, zip).
Open form and try.

 

[andy_25]

Thanks guys those responses were very helpful. I was hoping it was going to be easier than that. I have used a combination of the 2 to come up with a solution.

I passed the data to textboxes (hiden) as with the example given, then passed them up into a single textbox. A bit of a mess I know, maybe MS will make it a bit simpler in future, ORACLE application builder can do it a lot easier, then it is a bit more expensive

 

[CBrighton]

It only requires the editing of 2 properties on the combobox.

One to tell it there are n columns and another to tell it how wide each column should be.

 

[andy_25]

It only requires the editing of 2 properties on the combobox.

One to tell it there are n columns and another to tell it how wide each column should be.

I have added the column widths so the user can view the columns. Where do I specify that there are n columns so the ComboBox displays the columns selected?

I cannot get it to show anything other than the bound column once selected. Any chance you can knock up a quick example?

 

[CBrighton]

Sorry, I'm a bit busy at the moment.

However I have attached a screenprint of a single column combobox in one of my database with the column count property highlighted.

 

[missinglinq]

When it is clicked it shows their FirstName, LastName and JobRole.

However once they have made the selection it only shows the first value i.e. Andy

Is there a way of getting the ComboBox to show all 3 values once one has been chosen?

No one has read the question properly! The OP wants all three fields to appear in the Combobox after a selection has been made!

If your form is based on a Query, go into the Query in Design View. If you don't, you need to go into Design View for Queries and create a Query with all the data from your Table. Now go to a blank field in the Query Grid and type this in:

FullName: [FirstName] & " " & [LastName] & " " & [JobRole]

Now go into Form Design View and replace the RecordSource with your Query, if it is not already the RecordSource. Delete your old Combobox and create a new one. This time, when the Wizard comes up and asks for the Table or Query to retrieve the data from, select your new Query. Next choose FullName as the field for your Combobox.

That should do it. When you make a selection all three pieces of data will display.

Linq ;0)>

 

[CBrighton]

No one has read the question properly! The OP wants all three fields to appear in the Combobox after a selection has been made!

If your form is based on a Query, go into the Query in Design View. If you don't, you need to go into Design View for Queries and create a Query with all the data from your Table. Now go to a blank field in the Query Grid and type this in:

FullName: [FirstName] & " " & [LastName] & " " & [JobRole]

Now go into Form Design View and replace the RecordSource with your Query, if it is not already the RecordSource. Delete your old Combobox and create a new one. This time, when the Wizard comes up and asks for the Table or Query to retrieve the data from, select your new Query. Next choose FullName as the field for your Combobox.

That should do it. When you make a selection all three pieces of data will display.

Linq ;0)>

I read it properly, but thought my suggestion would do that.

It's been a while since I used multi-column comboboxes for anything other than hiding the bound field and displaying others.

But you're right, the easiest way to do this would be with a concatenated field in a query and use that as the rowsource.

 

[missinglinq]

Regardless of the Column Widths, only the first visible Column displays after a selection has been made. You have to Concatenate the fields in order to display all of them after selecting.

 

[CBrighton]

Yup, I just conceeded that.

My bad, I put forward a solution I remembered without testing it before posting, turns out I was wrong.

 

[missinglinq]

Everybody does that, from time to time! And I mean everybody! The important thing is that you took the time to help!

 

[andy_25]

No one has read the question properly! The OP wants all three fields to appear in the Combobox after a selection has been made!

If your form is based on a Query, go into the Query in Design View. If you don't, you need to go into Design View for Queries and create a Query with all the data from your Table. Now go to a blank field in the Query Grid and type this in:

FullName: [FirstName] & " " & [LastName] & " " & [JobRole]

Now go into Form Design View and replace the RecordSource with your Query, if it is not already the RecordSource. Delete your old Combobox and create a new one. This time, when the Wizard comes up and asks for the Table or Query to retrieve the data from, select your new Query. Next choose FullName as the field for your Combobox.

That should do it. When you make a selection all three pieces of data will display.

Linq ;0)>

That is exactly what I was looking for. I worked it out before reading your response, but thanks

 

[RenaG]

I need to do this too.

My combobox is based on an SQL statement (not a stored query). I added the FullName field as you outlined to my SQL statement. Where do I add the FullName in the Property Sheet? I tried putting it in the Control Source but then the combobox quit qorking. It drops down but doesn't accept my selection.

TIA!

~RLG

 

[CBrighton]

I need to do this too.

My combobox is based on an SQL statement (not a stored query). I added the FullName field as you outlined to my SQL statement. Where do I add the FullName in the Property Sheet? I tried putting it in the Control Source but then the combobox quit qorking. It drops down but doesn't accept my selection.

TIA!

~RLG

To confirm, are you trying to store a concatonated field like "[FirstName] & " " & [LastName] & " " & [JobRole]" in a single field after the selection is made?

While the concatonated field is good for displaying the data, I can't see why you would want to store it in that format.

Which properties you want to set really depend on what the combo box will actually be used for.

 

[RenaG]

Hi~
No, I don't need to store the concatenated fields, I just want to display the whole name in the combobox once it is selected.

But your questions got me to thinking and experimenting. Since I put the concatenated field in my query, I changed the number in Column Count and the Column Widths so that it just displays the last column (which contains the concatenated fields). That works perfectly.

Thanks!

~RLG