Removing duplicates from a drop down list

[JeffreyDavid]

On my form I have a subform, and on my subform, I have 3 cascading combo boxes, all pulling from the same table. Does anyone know how to or if it is even posible, I am looking to remove all of the duplicates from my 1st drop down box. Other than going through the table and doing so manually.
Ideas anyone?

 

[Uncle Gizmo]

If your data is redundant you are better off deleting it.

However it is possible to do what you want to do by making a query based on the table, And set the queries properties to return Unique values, Use that query as the data source for your combo box.

 

[JeffreyDavid]

In the Row source property of the combo box, a SQL statement is created to show the correct field. If the begining of the SQL statement has 'SELECT DISTINCTROW' , remove 'ROW', so it shows 'SELECT DISTINCT'. Now the list does not display duplicates.

 

[Uncle Gizmo]

Yes, That sounds right to me, Without having a look. You can do this in the query builder by right clicking and selecting unique values.

 

[dtpetty]

I am trying to do something similar. The SQL I get for the drop-down does not include either DISTICNT OR DISTINCTROW. It is also bringing in automatically anothe field which is the primary key for that table. Any attempt to edit the SQL statement to DISTICNT or DISTINCTROW or remove the other field all cause the list to be empty. Thanks in advance for any help.

 

[billyr]

Being a novice is not all bad. When I have a complicated cdo or list box to build, I use a stored query design. When I get that working like I want, I switch to sql view, copy and paste the sql statement into the row source property for the 'box and delete the (now) unwanted query. I'm sure I got that idea reading these forums. It works for me.

 

[pono1]

Being a novice is not all bad. When I have a complicated cdo or list box to build, I use a stored query design. When I get that working like I want, I switch to sql view, copy and paste the sql statement into the row source property for the 'box and delete the (now) unwanted query. I'm sure I got that idea reading these forums. It works for me.

You can also save that query and then put the query name in the row source. Either way eases the pain...

Regards,
Tim

 

[JeffreyDavid]

I am trying to do something similar. The SQL I get for the drop-down does not include either DISTICNT OR DISTINCTROW. It is also bringing in automatically anothe field which is the primary key for that table. Any attempt to edit the SQL statement to DISTICNT or DISTINCTROW or remove the other field all cause the list to be empty. Thanks in advance for any help.

If you right click on the combo box and select Properties. In the property's window, click in the Row Source property. A drop down button appears and another button with 3 dots at the bottom. With the cursor flashing in the Row Source property, it doesn't say 'SELECT DISTINCT ...'? If not, try recreating the query.

 

[Wolfking]

If your data is redundant you are better off deleting it.

However it is possible to do what you want to do by making a query based on the table, And set the queries properties to return Unique values, Use that query as the data source for your combo box.

This worked for me. Had to hunt it down. While in the query builder click on the background to switch to the query properties then change the option 'unique values' to yes.