Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: Deleting duplicates in Listbox Help for a Newbie

Page 1 of 2 12 LastLast
Results 1 to 15 of 22
Share This Thread →
  1. #1
    dpn
    dpn is offline Registered User
    Join Date
    Nov 2012
    Posts
    13
    Rep Power
    0

    Deleting duplicates in Listbox Help for a Newbie

    Hello all, I searched all around but cant find the answer to my question.

    I am doing an assignment for school, where I make a form with a list box choosing property type and a range of currency to enter then after my command button the results are displayed in a report.

    Here is the problem:

    The table I'm drawing the data from has a row called "property type" in which there are villa, manor and castle mutiple times. I made a query but when it puts the data into the form ALL of the castles and villas show up in the listbox, where as I just want one instance of each.

    Ex:
    (how listbox looks now)
    castle
    castle
    castle
    villa
    villa
    villa
    manor
    manor
    manor

    (how I want it too look)
    castle
    villa
    manor

    I'm stuck and my teacher is pretty useless when answering my questions. Maybe because it's online but man is he vague....it's already handed in but I can make revisions for more points.

    Thanks for any help, I'm freaking out over here lol

  2. #2
    meratigoerr's Avatar
    meratigoerr is offline Contributing User
    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    In the query properties you can set the "Unique values" property to yes. That should limit it to one of each value returned in your query.

  3. #3
    alansidman's Avatar
    alansidman is offline Contributing User
    Join Date
    Feb 2008
    Location
    Lake County, IL
    Posts
    864
    Rep Power
    152
    An alternative to meratigoerr's solution is to use group by in your query to arrive at unique entries. Look at this video.

    Macromedia Flash (SWF) Movie Created by Camtasia Studio 2
    Alan Sidman
    Office 2010, 2007, 2002

    If I helped you, then click "give rep" button in the lower left corner.

  4. #4
    meratigoerr's Avatar
    meratigoerr is offline Contributing User
    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    Well gee Alan... if you want to do it the easy way.... (by the way, love the datapig videos. Have helped me so much.)

  5. #5
    dpn
    dpn is offline Registered User
    Join Date
    Nov 2012
    Posts
    13
    Rep Power
    0
    Thank you for the quick replies.

    I tired both hose methods and they didn't work. Maybe because I forgot to mention I have mutiple fields?

    I did find a part solution... I changed the row source property on the listbox itself and the query on it has one field and the results aren't duplicate... but, although they work as intended, the list items do not show up!

    So now I can click on the spaces where the items in the list box are, Have the query work without duplicates, but the text doesn't show up... crap.

  6. #6
    dpn
    dpn is offline Registered User
    Join Date
    Nov 2012
    Posts
    13
    Rep Power
    0
    *The text for the query selections doesn't show up in the listbox to select them, just blank spaces.

    I think that makes it more clear than my last post, sorry!

  7. #7
    alansidman's Avatar
    alansidman is offline Contributing User
    Join Date
    Feb 2008
    Location
    Lake County, IL
    Posts
    864
    Rep Power
    152
    I tired both hose methods and they didn't work. Maybe because I forgot to mention I have mutiple fields?
    What does this mean? Perhaps you should post a sample of your multiple fields. I don't understand.

  8. #8
    dpn
    dpn is offline Registered User
    Join Date
    Nov 2012
    Posts
    13
    Rep Power
    0
    For sure it's an assignment, should I just post the whole db? list box in frmType and related qryImrprovedParameter are what's killin me lol

  9. #9
    meratigoerr's Avatar
    meratigoerr is offline Contributing User
    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    Neither of those suggestions will work with multiple fiels.. Yes, you probably should have mentioned that from the start. Simplest solution is to create a query from your query with only the one field you need for your listbox. Then either suggestion will work. (although I will admit that Alan's suggestion is the better route to go than mine.) To then get your list of items that contain the type selected in the list box, create a query that uses the list box as criteria to populate your list. In the on change event of your listbox add code to requery the list.
    Last edited by meratigoerr; November 26th, 2012 at 01:46 PM.

  10. #10
    dpn
    dpn is offline Registered User
    Join Date
    Nov 2012
    Posts
    13
    Rep Power
    0
    Here are some pics of my dilemma, I'm trying what you guys are saying but I'm doing it wrong. As I said I'm a newbie but this is the first assignment giving me trouble so please don't laugh too hard.


  11. #11
    dpn
    dpn is offline Registered User
    Join Date
    Nov 2012
    Posts
    13
    Rep Power
    0
    Posted some pics just waiting for them to clear mods

  12. #12
    June7's Avatar
    June7 is offline Moderator
    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,618
    Rep Power
    649
    I am afraid to ask - do you have lookups set in tables?

    I presume the displayed query is RecordSource for report?

    We are going in circles. Upload db to a fileshare site such as box.com and post link to the file. Run Compact & Repair and zip file. If there is confidential data, make copy and remove that data.
    I like to know if my suggestion helped you. One way to let me know is by clicking star at bottom left corner of post.
    Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm

  13. #13
    dpn
    dpn is offline Registered User
    Join Date
    Nov 2012
    Posts
    13
    Rep Power
    0
    Quote Originally Posted by June7 View Post
    I am afraid to ask - do you have lookups set in tables?
    No, how would that be relevant? Like I said I'm learning

    Quote Originally Posted by June7 View Post
    I presume the displayed query is RecordSource for report?
    Yes

    Quote Originally Posted by June7 View Post
    We are going in circles. Upload db to a fileshare site such as box.com and post link to the file. Run Compact & Repair and zip file. If there is confidential data, make copy and remove that data.
    Will do.

  14. #14
    dpn
    dpn is offline Registered User
    Join Date
    Nov 2012
    Posts
    13
    Rep Power
    0
    https://www.box.com/s/pt8drv3mgpbz5nx62b21

    Thanks for all the help everyone.

  15. #15
    meratigoerr's Avatar
    meratigoerr is offline Contributing User
    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    ok... the form [frmType] is pretty sparse. I'm assuming you want it to fill in the rates automatically from the choices you select, but you'll going to need more infor in your query to do it.
    Code:
    SELECT tblProperty.PropertyType, Max(tblProperty.NightlyRate) AS MaxofRate, Min(tblProperty.nightlyRate) AS MinofRate
    FROM tblProperty
    GROUP BY tblProperty.PropertyType;
    Using the above query for the record source of your list box will give you the single types your looking for, as well as the min and max values to populate into the range fields you have on the form. You can display just the property type and hide the min and max values. You would need to either add code the on change event of the list box, or reference the list box directly from your min and max fields.

Page 1 of 2 12 LastLast
Share This Thread →

Become Part of This Conversation

Join NowFor Free!

Similar Threads

  1. Deleting Duplicates in a Query in Access
    By chrisjack001 in forum Microsoft Access Help
    Replies: 5
    Last Post: January 7th, 2011, 06:47 AM
  2. Reorder Items a Listbox - Help for Newbie
    By ljuliusson in forum HTML, JavaScript And CSS Help
    Replies: 1
    Last Post: April 25th, 2007, 05:05 AM
  3. selecting multiple values in listbox and deleting them
    By hithere in forum Visual Basic Programming
    Replies: 1
    Last Post: August 31st, 2005, 03:20 PM
  4. deleting duplicates
    By kamberman in forum Microsoft Access Help
    Replies: 1
    Last Post: November 15th, 2004, 03:32 AM
  5. Listbox Duplicates
    By eddy99 in forum Microsoft Access Help
    Replies: 2
    Last Post: July 28th, 2004, 10:15 AM

ASP Free Advertisers and Affiliates