Select item from a ComboBox

Question

I have Access 2010 form which has a ComboBox cmbSubTopic which lists two columns (SubTopicID and SubTopic). The combo box is bound to a field containing SubTopicID. The SubTopicID column in the combo box is hidden, it only shows the SubTopic. When the user selects a SubTopic from the drop down the corresponding SubTopicID is stored in the table. I wrote some VBA code for the on load event of the form to look up the SubTopicID in the table and the corresponding SubTopic is selected in the ComboBox. My current code is something like this:

Set rsST = dbs.OpenRecordset(strSqlst)
For i = 0 To Me.cmbSubTopic.ListCount - 1
    If Me.cmbSubTopic.Column(0, i) = rsST.Fields("SubTopicID").Value Then
        Me.cmbSubTopic.SetFocus
        Me.cmbSubTopic.Selected(i) = True
        Exit For
    End If
Next i

This gives the error saying:

The text you entered isn't an item in the list

I also tried using this:

Me.cmbSubTopic = Me.cmbSubTopic.Selected(i)

This selects the item in the ComboBox but it also writes the value of I in to the ID field of the table which I don't want.

Answer 1

Assuming the combo's first column, SubTopicID, is also its "bound column" property, that column's value is used as the combo's .Value property. That means you only need to assign a value to .Value in order to select the matching combo row.

Me.cmbSubTopic.Value =  rsST.Fields("SubTopicID").Value

That approach is simple, but I'm uncertain whether it is the appropriate solution for your situation. We don't know anything about your rsST recordset --- I presumed the SubTopicID field in the recordset's current row is the value you want selected in the combo. If I misunderstood that point, we need to figure out something different.

If the combo is bound to a field in the form's record source, this suggestion would also change the stored value. If you don't want that, "unbind" the combo --- in other words, make its Control Source property blank.