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.

Youthen

Generally you have nothing to do for the combobox having a controlsource, as its value is that of the underlyng table field. – Pupillary

Is there a way I can block inserting the data into the table on the OnLoad event of the form and then allow insert on the AfterUpdate event of the combobox. – Youthen

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.

Triform

Answer 2

You can assign ItemData(idx) to your combobox:

     'Change the combobox slection to the second item
     '(index starts with zero)

     cmb = cmb.ItemData(1)

Note that I have been working with MS Access 2007. The more current version might not allow this assignment anymore.

Sestina

Answer 3

Here are 2 subs - one for passing text (SubTopic) and the second for ID (SubTopicId):

Public Sub SelectComboBoxItemByText(cmb As ComboBox, Value As String)
    On Error GoTo ErrHandler_
Dim i As Integer
    For i = 0 To cmb.ListCount - 1
        If cmb.Column(1, i) = Value Then
            cmb.SetFocus
            cmb.Selected(i) = True
            cmb.Text = Value
            Exit For
        End If
    Next i
End Sub
ExitProc_:
    DoCmd.Hourglass False
    Exit Sub
ErrHandler_:
    DoCmd.Hourglass False
    Call LogError(Err, "basTools", "SelectComboBoxItemByText")
    Resume ExitProc_
    Resume ' use for debugging
End Sub

Public Sub SelectComboBoxItemById(cmb As ComboBox, Value As Integer)
    On Error GoTo ErrHandler_
Dim i As Integer
    For i = 0 To cmb.ListCount - 1
        If cmb.Column(0, i) = Value Then
            cmb.SetFocus
            cmb.Selected(i) = True
            cmb = Value
            Exit For
        End If
    Next i
ExitProc_:
    DoCmd.Hourglass False
    Exit Sub
ErrHandler_:
    DoCmd.Hourglass False
    Call LogError(Err, "basTools", "SelectComboBoxItemById")
    Resume ExitProc_
    Resume ' use for debugging
End Sub

Jazzman