Hi,

I have a combo box that uses a simple query as the recordsource. There are 4 columns including the key.

I have set the column count to 4 and the lengths are 0";1";1";1"

When I select the dropdown I see the three columns I need. However, when I select a specific row and the dropdown closes all that is seen in the combobox on the form is the 1st column.

Example:

When I select the Dropdown I see:
Smith Joe Employee
Jones John Contractor

If I select the second row, the combobox collapses (which is good) but all that remains visible is the last name of the row selected:
Jones
I want to see all three columns in the combobox of the selected row, and not just the first one.

I cannot find any properties that would fix this. I am using MS Access 2010 but this problem existed in 2007 as well.

In the past I worked around it by concatenating the columns I want to see in the first column but frankly this is a pain.

Thanks for your help!!

I am new to access but you will need to modify your query as:
1st column ID remain
2nd column should be an expression. right click build > select "table" select the column you want then +" "+ then select the second column then +" "+ third column etc...
Also change the value from 0,1,1,1 to 0,5

This is just the way a ComboBox is built and works. It is made of a ListBox associated to a TextBox. When the combo is "closed" (i.e. the list is invisible) it behaves as a TextBox, which can only display (and allow editing) one value. There is no property or method that would allow you to change that.

What you can do, if you really want to, is to associate several TextBox controls to the ComboBox, using its AfterUpdate event to update the TextBoxes contents.

I suspected as much. Thanks for the feedback!

I like your idea and it would be fun to try it out just to say I did it. But, I think I'll just stick to concatenating the important fields into column 1.

I'll add this as a follow-up question: I would like to have the columns separated by a fixed length so they are easier to read. I tried padding with spaces and they didn't line up. This is probably because of the font. How can I change the font for the combo box? I looked at the properties and it didn't jump out at me.

Thanks!

Perhaps you can provide guidance on how to allow the columns to line up.

For example, if I concat last_name + ", " + first_name + " " + phone_number I get this in the ComboBox

Jones, John 555-1212
Murphy, Frank 444-1212
Wilsonnator, Louise 333-1212

I want them to line up.

I tried String to pad it like this: last_name + ''," + string(20-length(last_name)," ") + first_name etc...

But they still don't line up. I suspect the font is at fault. Any ideas on how to fix? (It just occurred to me to try placing an ASCII tab between the columns. This might work, I suppose)

Thanks

The only way that you could get the columns to line up would be to use a fixed-width font for the combo box, and to pad all the input columns to a set length. Using a tab character would smooth things out a bit, but you would still get some records that were longer than others.

You suspect right!

If this is the path you wish to go down (which, personally, I wouldn't), then you will need to use a monospace font. i.e. Courier.

Courier makes sure that every character takes up the same amount of pixels on your screen. Whether it's a 'W' or a ','. For example, the [ CODE ] tags for these posts use courier, so:

Gives you the idea.


Personally, I would avoid going down this route, because you'll run into problems if you get a long name for example.

You can't use vbTab in as part of the strings that are listed in the combo box, so that's a no go.


I would:
1) Make a listbox, that's only tall enough for one row, with your 4 columns.
2) I'd then set an 'AfterUpdate()' event for it, to change its height, from one row, to x rows, and vice versa, giving the impression of it being a combobox.
3) I'd then set a 'LostFocus()' event for it, to ensure that it returns to one row, if a user doesn't make a choice, and moves to a different control.

So, so far you have a piece of code that looks like:

The ListIndex is used to allow the bottom items of the list (that are effectively 'cut off', when the list is shrunk back to one row) to remain in view when selected.


If you wanted to get more technical about it, there are a couple of things that aren't particularly nice about it. For one, the \/ icon that is usually next to the combobox, obviously won't be there, instead you have an up and down arrow. This you could cover up with a label, that has its background/foreground colour set to match the colour of the form, and then simply toggle its '.visible' element to true or false, depending on whether the listbox has one row or several.

Another thing that isn't particularly convincing (for making it look like a true combobox) is that the selected item, the one that you'll see when you navigate to the next control on your form, will have a black, highlighted, background. This can be overcome by:
1) Storing the selected value(s) in a Public variable/array.
2) Then adding immediately after step 1, 'tmpList.Selected(tmpList.ListIndex) = False', to deselect it.
3) When you need to reference the value that is shown in the combobox, you would then simply reference the Public variable/array instead.


With those two slight modifications, you'd have exactly what you were after, but with a little more work and coding involved.

Thanks! What extraordinary input!

I will spend some time looking over your code and putting it to use. I am working on a relatively small application that will be used by several grant writers and project managers to track deliverables and to document various organizations, learning activities, attendees, etc...

It isn't an overly complex system but my users are very non-technical so every thing I can do to make it easier to use is a plus!

Thanks again.

-f