Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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

share|improve this question

2 Answers

up vote 3 down vote accepted

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
share|improve this answer
1  
to ListCount-1 :) I was just about to add this. – Remou Mar 12 at 16:44
Good spot! Changed :) – SoupyC Mar 12 at 16:52
Sometimes the best answer is the simplest - can't believe just looping through it never crossed my mind! – Kai Mar 13 at 10:00
up vote 1 down vote

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

 ComboBox.Value = Dlookup("Value","Table","Code='" & sCode & "'")
share|improve this answer
This works too. It's not based on a table at the moment, but probably will be at some point – Kai Mar 13 at 10:01
Kai, unless you have very few values, and by very few I mean 2 or 3, it should be based on a table for ease of maintenance and use in reporting and queries. – Remou Mar 13 at 10:12
I know - the whole thing will later be back-ended to SQL Server but at the moment am waiting for a full mapping list to be provided and am just cobbling together a UI design (with zero back-end) to show the client for initial feeback. – Kai Mar 13 at 10:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.