Access VBA: Find item in combo box based on non-bo

Question

I have a two-column combo box on an Access form representing a key-to-code mapping. The first column of the combo box is the 'bound column' (ie, the column used when MyComboBox.Value is called).

I need to dynamically set the Value of my combo box based on a value found in the second column. For eg, if my combo box source is:

Value | Code
===============
 A1    | ABCD
 A2    | EFGH
 A3    | IJKL

I can set the value of the combo box simply with ComboBox.Value = "A2", but how would I do the same using the second column? ComboBox.Value = "EFGH" obviously isn't valid. Essentially looking for logic along the lines of ComboBox.Value = ComboBox.ValueWhereSecondColumnEquals("EFGH")

Answer 1

And assuming it's not based on a table/query:

Dim i As Integer

For i = 0 To ComboBox.ListCount-1
    If ComboBox.Column(1, i) = "EFGH" Then
        ComboBox.Value = ComboBox.ItemData(i)
        Exit For
    End If
Next i

Answer 2

Assuming that your combo is based on a table, you can DLookUp the value in the table:

 ComboBox.Value = Dlookup("Value","Table","Code='" & sCode & "'")

Answer 3

If the source is the value selected in a combobox, and the target is an unbound combobox set the .BoundColumn property of the target combobox to the appropriate column and then just assign the combobox values normally, as in cboX=cboY. Even if the target combobox is bound, you can dynamically change the bound column as needed.