8

Embarrassingly simple question but I can't work it out or find the answer via google.

Got something like this with two colums

contacts dropdown

But when selected it only displays one column, making the information much harder to read/ understand.

contact

Tried changing properties in property sheet (such as column number) but to no apparent effect.

user137263
  • 747
  • 5
  • 11
  • 25

3 Answers3

4

It depends to a certain extent on what you are doing, often something like this suits:

SELECT Id, Surname & ", " & Forename from Table

In other words, the bound column is a unique ID and the selection column includes both the surname and forename in a single column.

EDIT based on additional information:

SELECT [Contact].[CID], [Contact].[Csname] & ", " & [Contact].[Cfname] 
FROM [Contact] ORDER BY [CID], [Csname], [Cfname];
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • 1
    Thanks, but the SQL is already .... SELECT [Contact].[CID], [Contact].[Csname], [Contact].[Cfname] FROM [Contact] ORDER BY [CID], [Csname], [Cfname]; it is automatically expanding (when clicked upon) to include both columns (the ID column is hidden) - but unfortunately collapses otherwise. May just have to bundle first and last names into a single column! – user137263 Aug 01 '12 at 15:45
  • That is what I was suggesting. The alternative is to include textboxes to write the columns out to, but concatenating surname with forename is usually a more pleasing solution. – Fionnuala Aug 01 '12 at 15:47
2

Since my first column is used to group items in the second it didn't look nice to just separate them using

SELECT ID, [Type] & ": " & [Title] AS BothValues

as suggested. With a lot of items the dropdown looks confusing like in this example.

I found out that Access seems to display the first (technically) visible column in the combobox after a selection has been made. Therefore I ended up using

SELECT ID, [Type] & ": " & [Title] AS BothValues, Type, Title

and setting the width of the second column to the smallest possible, de facto invisible value (0";0.007";1",1"). Don't forget to raise the column count (4). Now the dropdown appears grouped, but I still get both information after the selection.

F1iX
  • 823
  • 6
  • 12
0

You can also use a visual workaround for this Access limitation, using a text box at the right of the combobox, and setting the textbox value equal to the third column of the combobox.

If the combobox is based on

SELECT [Contact].[CID], [Contact].[Csname], [Contact].[Cfname] 
FROM [Contact] ORDER BY [CID], [Csname], [Cfname];

Set column width equal to 0cm;5cm;6cm (or whatever you may need) and combobox width equal to the second column (5cm). Then create a new text box at the right of the combobox, without any space in between, set its width equal to the third column (6cm) and set textbox controlsource equal to:

=[combobox_name].[Column](2)

Textbox will not be modifiable by the user since it is associated to a control source.

Roberto Bonalumi
  • 1
  • 2