Microsoft Access - The field 'column bound' doesn't work properly

Question

I have problem with the 'column bound' option.

I want to create a combo box so that the field will show an other table field, and when I open the box it will show the first 3 columns. When I choose the row, I want it to save the 2nd value.

For that I defined the field like this:

1. Display control - combo box
2. Row source data - Table/Query
3. Row source - Customers
4. Bound column - 2 ; column count - 3

When I click and chose a row from the list that roll down, it shows me the first column value in the cell, instead of the second column value.

Can someone help me figure out the problem? (I check the field type and its fine - text)

Thanks in advance, Gal :)

Answer 1

You need to set the widths of the preceding columns to 0 if you don't want them visible. Otherwise when you bind the columns they show exactly as they are formatted in the table. i.e. if you want to display column 3, columns 1 and 2 need to be set in the display to Column Widths..... 0";0";1". You should notice when you expand the field that all three columns are displaying, but you only see the data from column 1 because the rest is being cut off in your box.

Answer 2

What you really want to do is set the dropdown as two values only, one hidden with the actual bound value and the other with whatever you want to display. For example if the 3 columns you wanted displayed are: [ID], [Name] and [Address] and you want [Name] to be your "bound" field your rowsource should be:

SELECT [Name], [ID] & ' - ' & [Name] & '-' & [Address] As DisplayField FROM Customers

Answer 3

As stated by 'mkinson' above; your table's field-level property 'Column Width' has to be set to 0" for ones which you would not want to show up inside your combo box. Example: If you have three columns, but you want to show only the column 2; In the table design view->Field properties->Column Width has to be set with a value 0";1";0". Similarly, if you want to show only the column 3; you would set the Field-level Column Width property to 0";0";1".

Answer 4

This can be confusing since the column order that is displayed in the dropdown list is NOT zero-based. In other words, the first column, whether it is displayed or not, is 1, not 0.

So, if you are selecting Col_ID; col_Name and col_Address for the list and you want to store col_ID but only display col_Name and col_Address, you would specify a Column Count of 3 with display widths of 0";1";2" but SET THE BOUND COLUMN AS 1, NOT 0.

However, in VBA, if you want to refer to the value in that hidden first column, the one we just called Column 1, it is referred to as column 0. Like cmb_Customer.Column(0).