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

Solution 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.

Solution 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;"

Solution 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.

Comments

[MAW74656]

I actually have these settings already, and I'm not seeing what I want. Perhaps I should clarify: I want the selected item to have [code] - LastName, FirstName. I already have it in the dropdown.

[MAW74656]

I already have the combobox databound to a query. Is the only way to change the display to add each item programmatically? I'm not seeing how this helps.

[Lance Roberts]

@MAW74656, ok, edited something, gotta run now, so I wasn't able to fully test things out (I'd probably need more detail of your exact setup also).

[Lance Roberts]

I've edited my answer, so you can remove the reference to my old answer. I like your solution the best.

[MAW74656]

Do I have to use a DLookup to get each field, or is there an easier way?

[Lance Roberts]

@MAW74656, I'm not sure, it depends on how the table was queried, but I think the answer you accepted is the best one, so it looks like you'll be ok.

[David-W-Fenton]

This is not an Access way of working with combo boxes -- the usual approach is to populate them with a SQL statement using data from a table.

[David-W-Fenton]

The above is not an answer -- you should either edit your original question to include it or add it to the question as a comment.

[MAW74656]

@David-W-Fenton -I disagree. The above explains what was done to solve the problem. That is an answer. But I didn't select it as THE answer because @RolandTumble had a very complete explanation of the issue. I don't see the problem here.