none
Populate Access ListBox with Distinct/Unique Values RRS feed

  • Question

  • I'm a CS student in my 4th year, my professor wont grade my assignment because i didn't populate a ListBox correctly (I got a 10/200 but i have an option to turn in 1 revision). The ListBox named "lstCateName" displays a property type (castle, villa, manor) from a table named "tblProperty" on a form called "frmType".

    My problem is with getting the ListBox to display only one of each Property type (Only 3 options instead of many same repeating options, had an image but it won't let me upload it on here).

    I need it to only display 3 options, "castle", "villa", and "manor". I have looked endlessly for the solution to no avail. There are some tutorials out there that focus on Excel and use VBA which i am not familiar with nor do i know how to implement into an Access Database.

    [EDIT]

    This is what i currently have in the property sheet for the ListBox under the Row Source

    [CODE]SELECT DISTINCT tblProperty.PropertyID, tblProperty.PropertyType FROM tblProperty ORDER BY tblProperty.[PropertyType];[/CODE]

    The PropertyID property is hidden form what i recall.
    • Edited by Globz Sunday, December 8, 2019 6:19 AM additional information
    Sunday, December 8, 2019 6:03 AM

All replies

  • Set the Row Source to

    SELECT DISTINCT PropertyType FROM tblProperty ORDER BY PropertyType

    Set Column Count to 1, and leave Column Widths blank.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, December 8, 2019 8:33 AM
  • The solution Hans has given you will solve your problem, but it does point to a more fundamental error in the database design.

    The fact that you apparently have to resort to returning values from the tblProperty table suggests that you do not have a tblPropertyTypes referenced table.  You should have such a table with a single column PropertyType, designated as the primary key.  The absence of such a table means that you are not able to enforce referential integrity in a relationship type between tblProperty and tblPropertyTypes, and the tblProperty table is consequently at risk of inconsistent values being inserted in the PropertyType column.

    You could of course prevent this in the user interface by binding a combo box with a value list as its RowSource property to the PropertyType column.  This would be poor relational database design, however, as it undermines the independence of the data from the interface by encoding data as part of an object definition, thus violating the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

    With a correct design the list box's RowSource property would be:

    SELECT PropertyType FROM tblPropertyTypes ORDER BY PropertyType;

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Sunday, December 8, 2019 4:58 PM Typo corrected.
    Sunday, December 8, 2019 4:29 PM