How do I reference a value from a list box in Column(0) on click?

Question

I have a list box that is populated based on a previous filter (a text box). This means there is no predefined number of items in the listbox so using the index is impossible.

What I am looking for is a way of selecting the value of column(0), as there are 3 columns in my list box, of the selected line that is clicked.

Private Sub listSource_Click()

Dim strSource As String

strSource = "SELECT [Product Code],[Stock Level],[Description] FROM [products/stock] " & _
"WHERE Mid([Product Code],1,5) = " & Me.listSource.Column(0).Value & ";"

Me.listResult.RowSource = strSource
Me.listResult = vbNullString

End Sub

What I think I need is just the small piece of code that is supposed to be where "Me.listSource.Column(0).Value" is.

Thanks in advance, Bob P

score 3 · Accepted Answer · answered Sep 27 '12 at 14:00

3

I reckon you want:

strSource = "SELECT [Product Code],[Stock Level],[Description] " & _
"FROM [products/stock] " & _
"WHERE Mid([Product Code],1,5) = '" & Me.listSource & "';"

Note the single quotes before and after Me.listSource.

answered Sep 27 '12 at 14:00

Fionnuala

90,370
7
114
152

Mate, your a king. Thanks to everyone who had a go! But this sorted it. – Bob P Sep 27 '12 at 14:04

SeanC · Answer 2 · 2012-09-27T14:04:29.020

1

Me.listresult gives the bound column

you then have to append .Column(0),.Column(1) ... for other columns.

to find out which one of the list you need to be looking at, .ItemsSelected.Count will tell you how many are selected (1 for a simple list box), and .ItemsSelected(0) for the index to the first item selected, so, the complete line for the first column of the first selected line would be:

Me.listSource.ItemData(Me.ListSource.ItemsSelected(0))

edited Sep 27 '12 at 14:04

answered Sep 27 '12 at 13:43

SeanC

15,695
5
45
66

1

A quibble: Column(0) is only the default value if the bound column is 1. – Fionnuala Sep 27 '12 at 13:59
@Remou, fixed. sloppy programming... having a bound column other than 1 never occured to me – SeanC Sep 27 '12 at 14:05

score 0 · Answer 3 · edited May 23 '17 at 12:27

0

You should be able to use Me.ListSource.Column(0) to get the value of the first column of the selected row.

Also see: Retrieve column values of the selected row of a multicolumn Access listbox

edited May 23 '17 at 12:27

Community

1
1

answered Sep 27 '12 at 13:42

KFleschner

499
3
13

Still not working guys, here is the code I am using. strSource = "SELECT [Product Code],[Stock Level],[Description] FROM [products/stock] " & _ "WHERE Mid([Product Code],1,5) = Mid(" & Me.listSource.Column(0) & ",1,5);" – Bob P Sep 27 '12 at 13:59

How do I reference a value from a list box in Column(0) on click?

3 Answers3