What is the difference between Control source and Bound Source in Combo/List Box?

0
votes

In the Property Sheet for Combo / List Box, we have Control Source and we also have the bound column.

How is control Source different from bound column?

1
ms-accessvba

1 Answers

1
votes

The Control Source is the field in a table your combo box is linked to, it could also be a query or an SQL statement. Either way it will display the data in that field and any updates you make to the combo box will be reflected in that field (unless it's a non-update-able query).
https://support.office.com/en-gb/article/ControlSource-Property-994c3208-c4e5-431d-8ec8-dabd5f91c77e

The Bound Column is linked to the Row Source. The Row Source tells the combo box what data is supplied to the combo box. Generally this will be the primary key of a list and a description of the items in that list, for example PersonID and PersonName.
https://msdn.microsoft.com/VBA/Access-VBA/articles/combobox-rowsource-property-access

The Bound Column tells the combo box which field of data in the Row Source to store in the Control Source field. So when you select PersonName the PersonID for that record will be stored.
https://msdn.microsoft.com/VBA/Access-VBA/articles/combobox-boundcolumn-property-access

Example

I have set up two tables in my database:
enter image description here
tbl_List contains four records used to populate our combo-box.

Main_Field_A in tbl_Main will hold the bound column value selected in the combo-box (this will be the Foreign Key linked to tbl_Lists Primary Key).
The Property Sheet for the combo-box is set up as below:
enter image description here
Note the Row Source Type is Table/Query.
We could type SELECT Field_A, Field_B FROM tbl_List; into the Row Source for the same result.
We could also manually enter the items in the list by typing 1;Item1;2;Item2;3;Item3;4;Item4 and changing the Row Source Type to Value List

In addition the Format tab has the Column Count set to 2 and the Column Widths set to 1cm;1cm. Usually the first, or bound column will have it's width set to 0cm so it is hidden.
enter image description here

In the below example I've changed the column widths to 0cm;1cm so the bound column is hidden.
enter image description here
As you can see it's entered the Primary Key value into the table, rather than the description.

The query to return the record would be: 

SELECT    Main_Field_ID
        , Field_B
        , Main_Field_B
FROM    tbl_Main INNER JOIN tbl_List ON tbl_Main.Main_Field_ID = tbl_List.Field_A

or you could use the Lookup Field functionality in Access, but that leads to the darkside.
http://access.mvps.org/access/lookupfields.htm