Hide Duplicates 1

[infinitx]

Hi,

I have a combo box that has a bunch of values. Some values repeat themselves. How do I hide those that are duplicates? I tried this:

"Form_ICL.Combo1.HideDuplicates"

But it gives me this error:

"Compile Error: Invalid use of property."

How do I hide the duplicate values in a combo box?

Thanks in advance!

 

[Tranman]

If your combo is based on a query, just say "Select distinct...".

Tranman

 

[infinitx]

Thanks for responding!

My Combo Box is based on a table and not on a query.

 

[Tranman]

But it *could* be based on a query that is based on that table...

 

[infinitx]

You're right!

Where do I input the "Select Distinct"?

Is it just "Select Distinct" or is it "Select Distinct.....something else"?
If something else, please state what I would put after "Select Distinct...."

Thanks!

 

[infinitx]

Sorry,

When I create a query and use that instead of the tables to create a form, when I go to view the form in "View Form" mode, the form is completely blank!

Is there any way to take out the duplicates using the items that are stored in tables?

Thanks!

 

[Tranman]

infinitx,
Your form has a data source (probably a table), and that should not be changed.

Your combo box also has a data source. If you view your form in design view and right click on the combo box in question, then choose "properties", you will see several tabs. Click on the data tab, and at the very top, there is a property called source. That should be set to the name of the field you want displayed in the combo box.

A little lower on this properties page, you will see something like "Row Source Type". There are options on this row like "Table/Query", "List"; etc. Choose Table/Query.

Below that, there is an item called "Row Source". This is where your select statement goes. It will need to say something like: Select distinct <fieldname> from <tablename>; The <fieldname> value will be the same value on the source line above. Do not forget to put square brackets around the fieldname and tablename values if you have space characters in the names.

Before you changed anything, the row source would have contained the name of the table where you were getting the values for the combo box. If you can get back to that point, just replace the table name with the select statement, and everything should work fine.

If you continue to have problems, just reply to this. I will stick with you until you get it nailed.

Tranman

 

[infinitx]

WOW!

Thanks a ton!

Good thing I saved a back-up copy before I did the query thing.

One more question if you don't mind, sometimes there is a need to input blanks ("") into the data table, but when I click to view what is in the combo box, the blank values appear also. How would I hide those?

Again, thanks a ton!

 

[Tranman]

infinitx,
I take it from your enthusiastic response that everything is working OK now.

Your last question has a couple of answers-depending on whether you are inserting blanks (empty strings which are equal to ""), or nulls, which are not equal to anything, and what the data type of <fieldname> is. My best guess is that nulls are what you're inserting, based on the fact that you are getting more than one blank row in your combo and the distinct should eliminate all but one of those.

Anyway, here is a solution to those blanks. Modify your SQL statement like so:

Select distinct <fieldname> from <tablename> where <fieldname> is not null and <fieldname> <> "";

If the data type of <fieldname> is numeric, this will cause an error (data type mismatch or something like that), and you will need to remove the "and <fieldname> <> "" part from the SQL statement. However, if <fieldname> is a text field, you will need to include the whole statement. Just try out the longer version, and if it does not error, leave it like that.

It was a pleasure lending a hand. Let me know if this solution works for you.

Tranman

 

[infinitx]

Thanks again!

Your help was priceless!

It worked! I just discluded "and <fieldname> <> """ and it worked perfectly!

Thanks again!