CODEWITHSUNDEEP
vbams-accesscombobox
Michael
Michael

Reputation: 3229

Set value of multicolumn combobox to all columns after selecting an item?

I have a multicolumn combobox with 3 columns in an Access 2013 form. If I choose one of the items in the combobox, the value that it gets set to is just the value from the 1st column. Is there any way to set the value of the combobox using VBA to all 3 values, just like when you were picking it? If you can't, then how can I set the value to the 3rd columns value instead of the first?

This is what my combobox looks like:

enter image description here

This is what it looks like after I select the first item:

enter image description here

Instead of just showing "1", I want it to show "1 | 2015 | 1.1" like it does in the dropdown list.

Upvotes: 0

Views: 2704

Answers (2)

user3598756
user3598756

Reputation: 29421

A workaround could be the following

  • place a label (say "Label1") above the combobox to hide it completely but its dropdown button

  • place in the userform code pane the following code:

    Private Sub ComboBox1_Change()
    Dim cbIndex As Long

    With Me
        cbIndex = .ComboBox1.ListIndex
        If cbIndex = -1 Then
            .Label1.Caption = ""
        Else
            .Label1.Caption = .ComboBox1.List(cbIndex, 0) & "|" & .ComboBox1.List(cbIndex, 1) & "|" & .ComboBox1.List(cbIndex, 2) & "|"
        End If
    End With
End Sub

this way the user will have all functionalities of the combobox while reading the whole combined text upon choosing one of its elements

Upvotes: 0

Andre
Andre

Reputation: 27634

You can't (except by concatenating the columns in the row source).

The displayed value after selecting an item is always the first visible column.
So to display the 3rd column, set the previous columns to width=0 (probably not what you want) or move it to the first place.

The Value of the combobox can be different from the displayed value, it is controlled by the Bound column property.

To access the other columns, use the .Column property. E.g. you can add a textbox after the combobox with a control source =myComboBox.Column(2) to show the 3rd column of the selected item.

Upvotes: 2

Related Questions