none
What's the best/easiest way to get round the problem of an access combo box and non unique values in the bound column? RRS feed

  • Question

  • I have a ComboBox based on a query that has no-unique values in the bound column. This means that the first non-unique entry of the column is selected rather than the actual row that was selected - I thought this was a bug as it seems such stupid behaviour but it seems to be 'working as designed'. 

    Anyway, I'd like to know what are the tried and tested and easiest and best ways to get around this problem? I've tried fiddling with the ListIndex property and making the ComboBox unbound and having a separate bound text box containing the selected now unbound column but keep running into all sorts of problems.

    Can anyone help?

    Sunday, October 6, 2019 4:25 PM

All replies

  • You need to include and bind your combo box to a unique identifier, is the primary key for that value.  That is the only way.

    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Sunday, October 6, 2019 5:22 PM
  • Or use Select Distinct
    Sunday, October 6, 2019 7:02 PM
  • Thanks for your answer.
    In my DISTINCT query uniqueness is only guaranteed across all columns and the column I want to use to be stored in the table (ControlSource) has to be the BoundColumn as I understand it but this is not unique. The first row (not the actual selected row) is always returned. I know this doesn't work as you say but do you know if there any easy/best workarounds to this problem?
    Sunday, October 6, 2019 8:04 PM
  • Thanks for your answer.
    In my DISTINCT query uniqueness is only guaranteed across all columns and the column I want to use to be stored in the table (ControlSource) has to be the BoundColumn as I understand it but this is not unique. The first row (not the actual selected row) is always returned. I know this doesn't work as you say but do you know if there any easy/best workarounds to this problem?
    Create and use a unique index as the bound column.
    Sunday, October 6, 2019 11:38 PM
  • So in the end I changed BoundColumn to 0 and used ListIndex to get the correct data for the selected row. However, I had to make the ComboBox unbound to the table by deleting the field name in the ControlSource property and create a new TextBox bound to the table with the field name in the ControlSource property which I then set at the Change event for the ComboBox.

    Thanks for all replies.
    Monday, October 7, 2019 8:19 AM
  • If a column in a table references a column in another table which can include duplicate values, the columns in both tables must be part of a multi-column composite key, foreign in the referencing table, primary in the referenced table.  Otherwise there would be ambiguity as to which row in the referenced table is being referenced.  The table structures would consequently be incorrect as a row in a referencing table must only reference a single row in a referenced table.  That is fundamental to the database relational model.

    The following image is of the relationships window in my ComboDemo file which can be found in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169




    The demo illustrates two alternative models for the local administrative areas of county, district and civil parish in my part of the world, one using conventional single column 'surrogate' numeric keys, the other using composite 'natural' keys.  Both are equally valid models, though the former would generally be used.  The numeric BoundColumn of a combo box in the case of the former is guaranteed to contain distinct values.

    In the case of the alternative model, using natural keys, then a combo box with a RowSource property such as SELECT Parish FROM Parishes ORDER BY Parish; would not contain distinct values in its single column, however.  This is overcome by correlating the combo box with other combo boxes in the form for selecting the county and district in which the location is located, using the following as the RowSource property of the combo box in which the parish is selected:

    SELECT Parishes_NatKey.Parish FROM Parishes_NatKey
    WHERE Parishes_NatKey.County = Form!cboCounty
    AND Parishes_NatKey.District = Form!cboDistrict
    ORDER BY Parishes_NatKey.Parish;

    Consequently the list of available parishes is restricted to those in the selected district and county.  The list is thus of distinct values, and no ambiguity can arise.

    Ken Sheridan, Stafford, England

    Monday, October 7, 2019 3:52 PM
  • well to anyone else reading this string; making a unique identifier is easy - - just merge fields together

    value       name        color

    1            ford             blue

    1            ford             red

    unique ID: [value]&[name]&[color]

    1fordblue

    1fordred

    there can be a valid reason for different selection choices resulting in the same value.  in this case the combobox's bound value is the UniqueID - but the value field is included in the selection (probably not visible) as column 3....  

    then you can refer/call that value as: me.combobox.column(3)

    Wednesday, October 16, 2019 2:29 PM