Duplicate Values in Combobox

[LemonRawr]

Hi, just to be up front that I'm pretty new to access. Would really appreciate the help.



I am trying to eliminate duplicate values in a few of my comboboxes but I am still getting duplicates even though I have unique values turned on. The three comboboxes are dependent on each other. So if combobox 1 and 2 has info, its should help filter combobox 3. And vice versa. But if combobox 1 and 2 are left empty, combobox 3 should show all possible values.

My 3 comboboxes are setup like this:

[Forms]![Formname]![FName] Or [Forms]![Formname]![FName] is Null
[Forms]![Formname]![LName] Or [Forms]![Formname]![LName] is Null
[Forms]![Formname]![Address] Or [Forms]![Formname]![Address] is Null

It appears I am getting 1 item in my combobox for every unique combination of FName, LName, and Address I have in my table that i get the info from.

It looks like I have a similar problem to this post. https://www.accessforums.net/showthread.php?t=59054
Doesn't seem like a solution was found though and the OP abandoned the thread.

[ranman256]

set the query in the combo box UNIQUE VALUES = TRUE.
This will remove dupes.

[John_G]

The duplicate values you see in the combo's are probably due to the row source in the combo's being incorrect.

Generally speaking a record selection process like that, using multiple dependant combo boxes, can work only in one direction, i.e. the list shown in Combo2 depends on the value of Combo1, and the list shown in combo3 depends on the values of combo1 AND combo2. But Combo1 does NOT depend on Combo2 or Combo3, and Combo2 does NOT depend on Combo3.

It appears I am getting 1 item in my combobox for every unique combination of FName, LName, and Address I have in my table that i get the info from.

That's very likely. What is the row source for combo1, and is it showing duplicates? What action do you take after making a selection from Combo1?

We'll start with that, and go from there.

[LemonRawr]

set the query in the combo box UNIQUE VALUES = TRUE.
This will remove dupes.

Unique Values is already set to yes.

The duplicate values you see in the combo's are probably due to the row source in the combo's being incorrect.

Generally speaking a record selection process like that, using multiple dependant combo boxes, can work only in one direction, i.e. the list shown in Combo2 depends on the value of Combo1, and the list shown in combo3 depends on the values of combo1 AND combo2. But Combo1 does NOT depend on Combo2 or Combo3, and Combo2 does NOT depend on Combo3.



That's very likely. What is the row source for combo1, and is it showing duplicates? What action do you take after making a selection from Combo1?

We'll start with that, and go from there.

My row source for combobox 1 is :

SELECT DISTINCT Table1.FName, Table1.YN, Table1.LName, Table1.Address FROM Table1 WHERE (((Table1.YN)=Forms!Formname!YN) And ((Table1.LName)=Forms!Formname!LName Or Forms!Formname!LName Is Null) And ((Table1.Address)=Forms!Formname!Address Or Forms!Formname!Address Is Null));

All 3 comboboxes are filtered by control YN. This one will always be selected first and will always have data. The other 3 fields are a bit more optional which is why I was hoping to have them filtered between one another depending on if the other data or not.

After making a selection in combobox 1, I have a after update macro that re queries the other comboboxes so the dropdowns are updated.

[LemonRawr]

Anyone else have any suggestions? Or is what I am asking not within the capability of access?

[John_G]

SELECT DISTINCT Table1.FName, Table1.YN, Table1.LName, Table1.Address FROM Table1

The row source for your combo is selecting 4 columns from Table1. But how many columns is the combo box displaying? The Select will give you one row for each unique combination of those 4 columns (with DISTINCT). But, displaying only one or two of the columns does not affect the number of rows (that's determined by the Select), so your combo will display duplicates.

For example, if your combo box Select returned rows of 4 columns:

red, green, blue, white
red, green, yellow, brown
red, green, blue, purple

but your combo box only displayed the first two columns, the combo would show red, green three times - apparent duplicates.

If you are using Select Distinct to prevent duplicates, only use the fields that are displayed in the combo in the Select.

[LemonRawr]

I have uploaded a sample copy of what I am doing. In the form "Forname", if i set YN to Red, LName and Address are both showing duplicate values

I think I see what you mean and why they are showing duplicates. Since I want to make the make my combo boxes dependent on one another, don't I have to include them all?