Strange behaviour from a combo box on a form when trying to evaluate data in another column

[GR4YB34RDD]

I have encountered a strange combo box bug.
I have as the datasource rows of title and the associated ID references.
As you can see from the drop down box the IDs are different in the combo.
But for some reason it does not return the correct ID, when selected, for the third entry.
See video:

I assume this is because the text entries are duplicated and it messes up the workings of the combo box in some way and ignores the ID. The data source for the combo also has the setting to return unique records so i cannot fathom why it is not returning the correct ID after the third selection because it is showing all the records, so evidently it considers them unique. I am aware from a logic point of view that having items with the same name is not wise and i will probably change the title data. Which will in turn probably fix the issue. I just wanted to know why this bug is happening in the first place because the 2nd column clearly shows the IDs are different but for some reason the combo box doesnt return it on the third option.
TESTING UPDATE: Changing the bound column from the text to the ID fixed it.
TESTING UPDATE: Changing the description to a different description also worked when i reversed the bound column change. I guess there is some weird workings going on somewhere that causes the box to bork on a duplicate bound text entry and when it finds the duplicate entry the internal workings just stop and dont update the references to the data in the other column.
TESTING UPDATE; Setting the bound colum to 0 also fixed it.
I guess this is more of a findings and theory post than a question post. Just thought id share as it seemed odd. I guess good practice would be to bind the column to something which has unique data inside it. This obviously may have issues further down the line if used in a similar way to the way i was using it. Which probably was not a very efficient or logical way. We learn from our mistakes though.
Regards,

 

[Gasman]

Could not see much from that video, too blurred.

 

[xavier.batlle]

As far as I know, when the first column displayed in a combobox has duplicated values, it always returns the first one.

 

[Gasman]

I have just created a combo with duplicate text entries as bound column, yet it returns correct value from second column?

 

[Pat Hartman]

I have just created a combo with duplicate text entries as bound column, yet it returns correct value from second column?

That is only accidental. Combos cannot "remember" which of the duplicates you selected which is why combos do not work with duplicate "keys"

 

[xavier.batlle]

I have just created a combo with duplicate text entries as bound column, yet it returns correct value from second column

I think that the attached database shows the OP issue. I say it's the normal behaviour.

 

[Ken Sheridan]

TESTING UPDATE: Changing the bound column from the text to the ID fixed it.

It will. The bound column of a combo box must contain distinct values, and is normally the key of the referenced table. In most cases this column is hidden. If the bound column does not contain distinct values, whichever of the duplicate values you select will map to the first instance of that value in the list, so, if a key column is hidden, it will map to whatever is the actual key for the first instance of the value selected, which might not be the row you selected.

Having duplicate values in a non-key column in a list is perfectly legitimate. I often tell the story of when I was present at a clinic where two patients arrived simultaneously, both female, both with exactly the same names, and both with the same date of birth. For some reason medical databases often rely on a combination of name and date of birth to identify people, so you can imagine the confusion which ensued for a little while. In this case a combo box's bound column would have been the patient's NHS number or similar, so its values would be distinct. However, because people don't usually remember their NHS number, the values used to log in would probably be just the date of birth and patient's name, which could be either of the patients. I generally recommend, therefore, that, in case like this, their address also be included in the list. The combination of the three values is unlikely not to be distinct, so selecting a patient will almost certainly be reliable.

Another way to differentiate duplicates would, in the right context, be to use correlated combo boxes, so that selecting a value in one combo box will result in values in a second combo box's list returning only rows which reference the value in the first combo box. In the attached little demo file three correlated combo boxes are used to select a country, then a region, and then a city. As shown in the demo this enables Paris, Texas and Paris, Île-de-France to de differentiated. Another advantage of this approach is that it enables the NotInList event procedure to be used to transparently enter a new city into the referenced Cities table, which would not be possible with a single combo box listing all cities, as Paris would already be a value in the combo box's visible column, so the NotInList event would not be triggered by typing in Paris again.

 

[GPGeorge]

I have just created a combo with duplicate text entries as bound column, yet it returns correct value from second column?

T

It will. The bound column of a combo box must contain distinct values, and is normally the key of the referenced table. In most cases this column is hidden. If the bound column does not contain distinct values, whichever of the duplicate values you select will map to the first instance of that value in the list, so, if a key column is hidden, it will map to whatever is the actual key for the first instance of the value selected, which might not be the row you selected.

Having duplicate values in a non-key column in a list is perfectly legitimate. I often tell the story of when I was present at a clinic where two patients arrived simultaneously, both female, both with exactly the same names, and both with the same date of birth. For some reason medical databases often rely on a combination of name and date of birth to identify people, so you can imagine the confusion which ensued for a little while. In this case a combo box's bound column would have been the patient's NHS number or similar, so its values would be distinct. However, because people don't usually remember their NHS number, the values used to log in would probably be just the date of birth and patient's name, which could be either of the patients. I generally recommend, therefore, that, in case like this, their address also be included in the list. The combination of the three values is unlikely not to be distinct, so selecting a patient will almost certainly be reliable.

Another way to differentiate duplicates would, in the right context, be to use correlated combo boxes, so that selecting a value in one combo box will result in values in a second combo box's list returning only rows which reference the value in the first combo box. In the attached little demo file three correlated combo boxes are used to select a country, then a region, and then a city. As shown in the demo this enables Paris, Texas and Paris, Île-de-France to de differentiated. Another advantage of this approach is that it enables the NotInList event procedure to be used to transparently enter a new city into the referenced Cities table, which would not be possible with a single combo box listing all cities, as Paris would already be a value in the combo box's visible column, so the NotInList event would not be triggered by typing in Paris again.

I have a similar, even more egregious story regarding common names and birthdates.

My wife took my pre-teen daughter to our doctor for a routine checkup. The doctor began asking my daughter questions that were obviously inappropriate for her age. My wife objected immediately. It turned out the clinic had two patients of the same name and similar birthdates. The other person was in her twenties, though. The person who grabbed the chart only checked the month and day of the birthdate apparently.

Lesson: the risks of assuming data is valid and appropriate are significant. Close enough doesn't work.