none
Combo Box List Rows are repeating RRS feed

  • Question

  • I have a Access 2016 form with a combo box that is tied to an embedded query. The query returns a list of ~7 items. The List Rows property for my combo box is set to 20. When a user clicks on the drop down box on the form, they see the 7 items repeating until there are 20 rows to choose from. I have my query set to return unique values, but there are three columns (one with the primary key) and I've set 0" column widths for the two columns I don't want (i.e., the Primary Key, and a filtering column) to be shown in the drop down list. 

    Two questions: 

    1) Why can't I set the List Rows property for a combo box to "Auto", so that the drop down list is populated with however many items is returned by my query?

    2) If I can't set it to Auto, how can I stop the combo box list from repeating items? 

    Thanks! 



    • Edited by le_kai Saturday, January 28, 2017 1:59 PM
    Saturday, January 28, 2017 1:56 PM

Answers

  • Hi le_kai -

    To answer your first question, a combo box could be driven by a table with thousands of rows. Setting such a combobox to 'auto' for the number of rows to display would require more display screen than available. The way it works now, if your query should only produce 7 entries, that dropdown list would only show 7 items, not the full list specified of 20. That is the evidence the query needs attention. In case another reader is curious, The number of display rows for a combo box is set using the 'list rows' property (on the format tab of the properties window) of the combobox.

    To fully answer question 2, .TheDbGuy is right - the SQL that fills in the combo is the key to the puzzle. Though you have it set to Unique, that does not mean that some data values won't be repeated. Unique usually means a unique combination of field values, regardless of what fields are hidden in the combobox. For example, if the query returns:

    ID | Color | CritterName
    1   |  Blue |    Toad
    2   |  Blue  |  Squirrel

    and you have columns 1 & 3 hidden, you will see 2 'Blue' entries to pick from.


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com


    • Edited by MainSleuth Saturday, January 28, 2017 4:31 PM spelling
    • Marked as answer by le_kai Friday, March 3, 2017 8:11 AM
    Saturday, January 28, 2017 4:31 PM

All replies

  • Hi,

    Just as a test, if you open the query designer/builder for the combobox, what do you see if you "run" it? Do you see duplicates? Can you post the Row Source SQL here? Thanks.

    Saturday, January 28, 2017 4:01 PM
  • Hi le_kai -

    To answer your first question, a combo box could be driven by a table with thousands of rows. Setting such a combobox to 'auto' for the number of rows to display would require more display screen than available. The way it works now, if your query should only produce 7 entries, that dropdown list would only show 7 items, not the full list specified of 20. That is the evidence the query needs attention. In case another reader is curious, The number of display rows for a combo box is set using the 'list rows' property (on the format tab of the properties window) of the combobox.

    To fully answer question 2, .TheDbGuy is right - the SQL that fills in the combo is the key to the puzzle. Though you have it set to Unique, that does not mean that some data values won't be repeated. Unique usually means a unique combination of field values, regardless of what fields are hidden in the combobox. For example, if the query returns:

    ID | Color | CritterName
    1   |  Blue |    Toad
    2   |  Blue  |  Squirrel

    and you have columns 1 & 3 hidden, you will see 2 'Blue' entries to pick from.


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com


    • Edited by MainSleuth Saturday, January 28, 2017 4:31 PM spelling
    • Marked as answer by le_kai Friday, March 3, 2017 8:11 AM
    Saturday, January 28, 2017 4:31 PM
  • Hi le_kai,

    as per your description I try to test on my side.

    but I did not get the results like yours.

    I assign the data using query.

    I also set the List Rows Property to 20.

    but I did not get any repeated value.

    refer example below.

    Following is the data in table.

    then I create a combo box. assign a query to it and set rows property.

    Query:

    Output:

    it displays correctly and not repeat the data until 20 records in combobox.

    I also try to test the same with more data.

    this time in a table there is 205 records.

    I set the List Rows = 20.

    then also it displays all 205 records.

    so here I assume that you had made some mistake and because of that you are getting output like that.

    please try to post your query here. so that we can try to look in that and try to find the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 30, 2017 2:20 AM
    Moderator