Display only 1 column in listbox but return another

[standish22]

OK, I have a record set that has say the following rows

ID Description
----------------------------------------------
1 Apple
2 Book
3 Cat

***************************************************************************************
I have done the following code to populate the listbox
(Design view Properties)
BoundColumn = 1
ColumnCount = 2
TextColumn = 2

(Code)

Set RS = cmd.Execute
Me.lstPayCycles.Clear
Me.lstPayCycles.Column() = RS.GetRows

***************************************************************************************
When the form displays it displays the ID and Description. I've tried
everything to get only the description only to display. See I want to
display the description, but then retrieve the ID, when I need to use
it.

I've tried setting Column Count to 1: That displayed the ID's, even
though TextColumn = 2

Any ideas???

Thanks...

 

[Nigel]

The bound column property determines the first column of data.

BoundColumn = 2
ColumnCount = 1

will show description only.

 

[Jon Peltier]

Keep column count = 2, but change the column width of one column to zero. In
the Properties window, for ColumnWidths, enter something like "90;0" without
the quotes, where the first column is 90 points wide and the second is zero.

- Jon

 

[Jon Peltier]

From the help file for TextColumn:

When the user selects a row from a ComboBox or ListBox, the column
referenced by TextColumn is stored in the Text property.

- Jon

 

[Jon Peltier]

From help file on BoundColumn:

"Assigns the value from the specified column to the control."

This means the value from the selected row of the listbox and from the bound
column is what is stored as the .Value of the listbox.

- Jon

 

[Nigel]

That was my point, if you need to display column 2 (The Description) set the
BoundColumn to 2. I accept it does not answer the OPs other quest to return
the ID #.

 

[Jon Peltier]

That's not how it works. If you need to display column 2, make sure you
specify that column 2 has a nonzero width. If you don't want to also display
column 1, make sure column 1 has a width of zero.

BoundColumn indicates which column of the listbox is returned when you query
ListBox1.Value. If BoundColumn is 1 or omitted, .Value returns what's in the
first column of the selected row. If BoundColumn is 2, it returns what's in
the second column of the selected row.

Similarly with TextColumn, which indicates which column of the listbox is
returned when you query ListBox1.Text. If TextColumn is 1, .Text returns
what's in the first column of the selected row. If TextColumn is 2, it
returns what's in the second column of the selected row.

- Jon

 

[Tom Ogilvy]

Since Jon did not name to property explicitly:

If you need to display column 2, make sure you specify that column 2 has a
nonzero width. If you don't want to also display column 1, make sure
column 1 has a width of zero.

which of course is done with the columnWidths property

me.Listbox1.columnWidths = 90;0

would hide the second column as an example.

Making the second column the boundcolumn would return the value from that
column for the value property as Jon stated.

 

[Jon Peltier]

Tom -

Thanks for clarifying.

Jon Peltier, Microsoft Excel MVP
http://PeltierTech.com