"Unique Values = Yes" and DISTINCT not eliminating duplicates

[kelann]

Hi, all!



I have a combo box (RespEventName) on a form (TrackSelectEventF).

The combo box's Row Source is as follows: SELECT DISTINCT ResponsesT.RespEventName FROM ResponsesT;

In the properties, by Unique Values, it says YES.

Still, I get duplicates -- my combo box list looks like this:

Entrepreneurship Event
Entrepreneurship Event
Find Your Niche
Find Your Niche
Find Your Niche

Rather than like this:

Entrepreneurship Event
Find Your Niche

Any thoughts on what I could be missing?

Much appreciated!

--ak

[pbaldy]

I would expect that to work, but you can try

SELECT ResponsesT.RespEventName FROM ResponsesT GROUP BY ResponsesT.RespEventName

If that does the same thing, I'd wonder if there were some hidden spaces or something in the values.

[kelann]

Your solution is getting me half there! I now have to selections ... but they're both blank!

I'm wondering if I got all 4 before, becuase the ID (the primary key) made each record "Unique" and "Distinct."

When I uncheck the primary key from the original mix, I get the same thing as your solution -- two choices (which it should be), but both blank!

Any further thoughts?

Thank you so much!

--ak

[pbaldy]

Your original SQL did not include an ID field. Certainly if one is included, you'd get the extra values for the reasons you specified. That would mean your combo probably has 2 columns with the first one hidden, which isn't what you want (I don't think).

[kelann]

I agree, and you are correct!

I used the query builder, and it keeps adding the ID field, and I get selections, but with duplicates.
When I take it out of the builder, I get the blank lines (no selections) -- but the correct number of them (2).
When I put in my statement -- or your statement -- directly into the SQL (thus bypassing the query builder), I get the same thing -- no selections, but the correct number of them (2).

Any more ideas, by any chance?

Much appreciated!!!!!

--ak

[pbaldy]

I guess I wasn't clear enough. Check the column count and column widths properties of the combo.

[kelann]

Ah! I think you solved it for me! Thank you!!!

It now says this in SQL:

SELECT DISTINCT ResponsesT.RespEventName
FROM ResponsesT
ORDER BY ResponsesT.RespEventName;

BUT, in the Format property for the Combo box, I switched Column Count to 1, and deleted the hidden column's 0.

It is now working!!!!

Many, many thanks!

--ak

[kelann]

Well, phooey!

It's working such that it only lists the two items.

However, as soon as you select one, it disappears and leaves an empty selection box.

There are no events related to either the form or the combo box.

Any thoughts?

So close ....

Thanks!

--ak

[pbaldy]

Is the form read only or the combo locked?

[kelann]

Ah, no, but you've pointed me in the right direction again! While looking for the "Read Only" property, I saw that the "Bound Column" was still listed as "2," even though I had changed it to be a 1-column combo box. When I changed the "Bound Column" to 1, it behaved correctly.

Thank you so much again for you assistance!! I really appreciate it!

--ak

[pbaldy]

Happy to help!