Deleting duplicates in Listbox Help for a Newbie

[dpn]

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

[meratigoerr]

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.

[alansidman]

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

[meratigoerr]

Well gee Alan... if you want to do it the easy way.... (by the way, love the datapig videos. Have helped me so much.)

[dpn]

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.

[dpn]

*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!

[alansidman]

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.

[dpn]

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

[meratigoerr]

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.

[dpn]

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.

[dpn]

Posted some pics just waiting for them to clear mods

[June7]

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.

[dpn]

I am afraid to ask - do you have lookups set in tables?

No, how would that be relevant? Like I said I'm learning

I presume the displayed query is RecordSource for report?

Yes

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.

[dpn]

https://www.box.com/s/pt8drv3mgpbz5nx62b21

Thanks for all the help everyone.

[meratigoerr]

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.

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.