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

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 & "'")