Results 1 to 6 of 6
  1. #1
    globz is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    3

    Question Populate Access ListBox with Distinct/Unique Values

    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 like in the image below)




    Click image for larger version. 

Name:	1.png 
Views:	18 
Size:	6.1 KB 
ID:	40403


    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.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Options:

    1. lookup table as source for the 3 items

    2. ValueList instead of Table/Query as RowSource

    3. SELECT DISTINCT PropertyType FROM tblProperty ORDER BY PropertyType;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    globz is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    3
    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];
    The PropertyID property is hidden form what i recall. Not sure i understand what you mean with option 1. For option two i only understand how to setup the ValueList, but am unsure how to link it to the same data that i will be using for the buttons like "open report" which currently uses the listbox.

    I appreciate all the help i can get, thank you!

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    A lookup table is a table that has a record for each of the items, tblPropTypes. Combobox RowSource would reference that table.

    You can't include PropertyID field. My example does not.

    If you want to open report filtered by property type:

    DoCmd.OpenReport "reportname", , , "PropertyType = '" & Me.lstCateName & "'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    globz is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    3
    THANK YOU! I needed a query open for the "OK" button and a report for the "Open Report" (cannot figure out the report still) button so i did some digging and figured out that everything i needed for the query you already provided me! I created a listbox with user defined values instead of ones from the form, and then went into the query i was using when i clicked the "Ok" button and i set the property type criteria to match the main forms user defined listbox. The query works like a charm now, but i cannot seem to get the report to work. The report just like the query need to know what parameter is set in the list box (I tried the above code and got an error that said "There are currently no printers installed on this computer"??). Here is the code that produced the printer error:

    Code:
    DoCmd.OpenReport "tblProperty", , , "PropertyType = '" & Me.List13 & "'"
    I hadn't mentioned it before since i figured it out for the query, but there is also a min price ("Text3") and a max price ("Text6") text box in the main form as well as the user defined list box (with the 3 options) which also needs to be a parameter for the report titled "frmProperty". I cannot figure out how to add them into the code above, or to even use a macro or something of that nature to get it to open with the correct parameters.

    I also need the main form ("frmType") to get opened again when i close the report ("frmProperty"), not sure at all how i would go about this.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The output destination is defaulting to acViewNormal which is direct to printer. If you want to view on screen then specify acViewPreview. And if you want to specify a range, use BETWEEN AND

    DoCmd.OpenReport "tblProperty", acViewPreview, , "PropertyType = '" & Me.List13 & "' AND Price BETWEEN " & Me.Text3 & " AND " & Me.Text6

    Review http://allenbrowne.com/ser-62.html

    I would not close form. Otherwise, code in report Close event to open form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Create a Field to Calculate Unique/Distinct Count
    By MikeNewAccessUser in forum Queries
    Replies: 3
    Last Post: 03-06-2015, 08:39 AM
  2. Replies: 1
    Last Post: 02-13-2015, 01:56 PM
  3. Replies: 1
    Last Post: 08-07-2013, 02:43 PM
  4. Replies: 10
    Last Post: 10-18-2012, 08:10 AM
  5. Replies: 2
    Last Post: 02-04-2011, 08:27 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums