Access combobox store 1 value, display another

Question

I'm have a query that returns the following columns:

Code
LastName
FirstName

I have a combobox where all of this info is displayed in the dropdown. But when I select a row, all I see in the combobox is the Code (its an employee number). What I'd like to do is display:

"[Code] - [LastName], [FirstName]"

as the selected item when a value is selected, and still store just the [Code] in the combobox's .Value property.

How is this done? I'm used to C#.NET where a dropdown has 2 properties (displayValue and selectedValue).

Answer 1

1. In this method you won't be able to have the formatting (dash or comma):

Set the column count to 3.
Set the Bound column to 1 (it's one-based, even though the .Column property is zero-based).
Adjust column widths to a pleasing arrangement.
Set RowSourceType to "Table/Query".
Set RowSource to your query.
Do not set a Control Source (leave blank--this leaves the .Value unbound from underlying data).

You can do all the above in Design View.

2. This method is more work, but gets exactly what you asked for:

In Design View:
Set column count to 2.
Set Bound Column to 1,
SetColumn Widths to 0";2" (accepts inches or cm, and if you just enter undecorated numbers will read them as inches (or as set in Options(?))).
Set RowSourceType to "Value List".
Do not set a Control Source (leave blank--this leaves the .Value unbound from underlying data).

Write this code:

Private Sub Form_Load()

'declare variables & open query as recordset--left as exercise

    With Combo1
        .Clear
        Do Until rs.EOF
            .AddItem rs.Code & ";" & rs!Code & " - " & rs!LastName & ", " & rs!FirstName
            rs.MoveNext
        Loop
    End With

    'close rs & clean up--another exercise

End Sub

The semicolon between the rs!Code instances in the string concatenation is what points them into the appropriate columns.

Answer 2

The following might help to add information to the combo box with 3 columns. Note that the column widths at the bottom is used to "hide" the first column

For x = 1 To 10
    ComboBox1.ColumnCount = 3
    With ComboBox1
        .AddItem "Code"                          ' Column 1 data
        .List(.ListCount - 1, 1) = "LastName"    ' Column 2 data
        .List(.ListCount - 1, 2) = "FirstName"   ' Column 3 data
        'etc.
    End With
    ComboBox1.ColumnWidths = "0cm;2.5cm;2.0cm"
Next

Hope this helps

EDIT:

DisplayString = code & " - " & Lastname & ", " & Firstname
ComboBox1.ColumnCount = 2
With ComboBox1
    .AddItem "Code"                             ' Column 1 data
    .List(.ListCount - 1, 1) = DisplayString    ' Column 2 data
End With

ComboBox1.ColumnWidths = "0cm;4.5cm;"

Answer 3

In the BeforeUpdate event (there might be a better one, that's just what I tested on), set the .Text property to the string you want.

Me.ComboBox1.Text = [Code] & " - " & [LastName] & ", " & [FirstName]

Note that you may have to play with the string construction.

Answer 4

I have it working the way I want now. Here's what I did:

-Did the concat in the sql, so the query returns columns [Code] and [DisplayName]. -Bound = 1, Columns = 2, Column Widths = 0:1"

Now the display value is what I specified in my SQL and the selected value (tested and confirmed) is just the code.

Thanks for the help, I'm not well versed in Access vba.