Select from drop down chooses wrong record

[kevvan]

I have a form that presents a user with a dropdown populated with a user name. The drop down control is bound to two things in a user table, the USERNAME and a related USERID. The drop down control bound to the second column which is the USERID.

When they make a selection from the dropdown, it populates a separate text box control with the USER ID value.

The problem is this...

When duplicate entries exist int he USERNAME field, no matter what the choice the USERID is populated with the value of the first USERNAME match.

For example:

USERNAME USERID
Jones, Robert 0001
Jones, Robert 0002
Jones, Robert 0003

If the user selects record three (Jones, Robert 0003) the form text box control that is to be populated with the user USERID will be populated with USERID value 0001 every time.

The control for the USERID display has the following control source:
=Forms!frmModLicense!cboUserSelect.column(1)

The desired result would be to have the text box populate with the 0003 value.
Anyone wish to venture a reason why this happens?


Thanks

[Stwange]

I have a form that presents a user with a dropdown populated with a user name. The drop down control is bound to two things in a user table, the USERNAME and a related USERID. The drop down control bound to the second column which is the USERID.

When they make a selection from the dropdown, it populates a separate text box control with the USER ID value.

The problem is this...

When duplicate entries exist int he USERNAME field, no matter what the choice the USERID is populated with the value of the first USERNAME match.

For example:

USERNAME USERID
Jones, Robert 0001
Jones, Robert 0002
Jones, Robert 0003

If the user selects record three (Jones, Robert 0003) the form text box control that is to be populated with the user USERID will be populated with USERID value 0001 every time.

The control for the USERID display has the following control source:
=Forms!frmModLicense!cboUserSelect.column(1)

The desired result would be to have the text box populate with the 0003 value.
Anyone wish to venture a reason why this happens?


Thanks

I'm guessing it's running a find first query, because the name is the bound field, not the ID. Try setting the bound column to be the ID instead, and see if that helps.

If not, here is another possibility:
Have two combo boxes, one hidden (to display the ID, visible/enabled = false)
Rowsource of the first: SELECT USERNAME FROM User ORDER BY USERNAME;
Rowsource of the second: SELECT USERID FROM User ORDER BY USERNAME;

Then on change/after update/whatever on the first one (the one displaying the username), do:

txtID.value = cmbID.itemdata(cmbUsername.listIndex)
and that should solve it

Hope that helps.