Combo box: show 2 columns in drop-display 2nd column after selection

[hilian]

I have a series of combo boxes that show two columns in the drop-down. I want to display the text from the second column in the box after a selection has been made (Access displays the first column by default). I’ve been looking for how to do this, and I’ve found ways to hide the first column, which I don’t want to do, and to display both columns, which I also don’t want to do.

There must be a simple way to show only the second column, but I haven’t found it.

Thanks,

Henry

 

[sneuberg]

You seem to be saying that you don't want to hide the first column but you only want to see the second column. What's wrong with hiding the first column if you don't want to see it?

Generally what you see or don't is controlled by the column widths. 0"; 1" for example would display only the second column.

 

[hilian]

The project involves reviewing case records. There are a large number of questions, and the reviewers will be entering their selections on paper data-entry sheets. We're not sure whether they will be transferring the data into the computer themselves or there will be separate data-entry people doing it--very possibly both. For the drop-downs, the reviewers will be using lists from which the the selections will copied to the data sheets. The list items are numbered. To save effort, they'll be writing the numbers on the sheet, rather than the actual selection. That's why I need to have the numbers displayed as well as the selections. The numbers are in the first column of the table that supplies the drop-down lists, and the selections are in the second column. The way I have it set up now, both the number and the selection show in the drop-down list, but only the number is displayed once the selection is made. I'd like the selection to display instead. That way, someone reviewing the selections can see right away what selection was made.

 

[sneuberg]

If it would be ok to see the number after the text in the drop down you could add an expression to the row source of the combo that's just repeats the number. Let say the number field was ID, the text field F1 then the row source could be something like:

SELECT Table1.ID, Table1.F1, Table1.[ID] AS DisplayID
FROM Table1;

You would then have three columns in the row source so you would set the Column Count to 3 and the Column widths to something like 0";1";1"

 

[hilian]

Steve,

Thank you. I think that's it! I don't have time to try it now, but I will on Friday (Happy Thanksgiving!)

I just tried something similar but based on the same idea. I created a query for the row source. The first two fields in the query were from the table, the third was an expression [ID] &". "& [Protocol] (The drop-down list concerns protocols that were used). The bound column remains 2, but the column count is now 3. the column widths are 0";0";1.5" so the third column, which concatenates the item number, a period and the item description, is the one that shows in the drop-down and as the selection. I didn't intend to have the number show once the selection was made, but that's very minor issue, and it may even be better than just having the selection alone showing. I checked the table, and the selection is stored in the field, as intended.

Many thanks,

Henry

 

[moke123]

try setting the column widths in the onEnter and afterupdate events - adjust for your column counts and combo names. As long as the focus leaves the combo, if they need to go back and change it, the enter event will set both columns back to visible. maybe have a setfocus to the next control in the after update event.

Private Sub Combo0_AfterUpdate()

Me.Combo0.ColumnWidths = "0in;1in "

End Sub

Private Sub Combo0_Enter()

Me.Combo0.ColumnWidths = "1in;1in"

End Sub

 

[hilian]

moke123,

Your solution gives me exactly what I want. And it's one of those ideas that's so obvious that you don't see it until somebody points it out. Thanks.

Now I have two ways of going about it: the way I found on Wednesday, and yours.

After some thought, I decided to use the way I found on Wednesday because that way I get something I didn't realize when I first posted the question that I should have. I get the number as well as the item description. Since some users will be working from the item description and others will be working from the number, I can meet both needs.

Again many thanks. I've expanded my Access repertoire, and that's a very good thing.

Henry

 

[sneuberg]

moke123,

Your solution gives me exactly what I want. And it's one of those ideas that's so obvious that you don't see it until somebody points it out. Thanks.

Did this actually work for you? I thought it was clever too but when I tried it it only worked the first time. The On Enter event did fire for me when I pick from the list from the second time on.

 

[moke123]

It worked for me. I added the setfocus to the next control on after update so that the on enter will fire again when you go back to it.

when I pick from the list from the second time on.

did you set the initial/default column widths to show both columns?

 

[sneuberg]

It worked for me. I added the setfocus to the next control on after update so that the on enter will fire again when you go back to it.

The form I created to test this only had the combo box. I see that it works with the second control and the setfocus. Thanks for the clarification.