Getting values from a VBA Listbox

[Leggie]

Hi guys,

In an Access 2007 database, I have a form with a ListBox that grabs the values from one of my tables. I want, when I click on a ListBox item, the value I have selected in the list to appear in a TextBox.

I can't seem to find the right code to do. I've tried many combinations, such as something like the following:

txtS.Value = lstS.ItemData(lstS.ItemsSelected.Item(0))

I've a feeling it must be easy and I can't see the wood for the trees. Should be a simple query, but I've waded through the VBA API and after working in Java it all seems much more vague and hazy.

Thanks for your help,

Leggie

[pipTheGeek]

Sorry, just tried finding the answer for you. Then I realised/remembered how much I HATE Access 2007. I'm going out instead. I had ended up with something similar to what you had posted, but my event handler appeared to be the wrong one becuase I was getting an error becuase the list had no items selected! If you are still stuck tomorrow then I'll sort the code out to do it in Access 2 instead.

(Access 2 is the version of Access that I used during the Access developer stage of my programming career.)

[Leggie]

I've had no more luck, I'm afraid, so any help would be gratefully received, thanks.

I'm also somewhat glad to see that it isn't just me that has trouble with Access!

Thanks,

Leggie

[yamangman]

I've no idea, but an educated guess would be:

txtS.Value = lstS.ItemsSelected.Item(0).Value

[scaryjim]

Good lord the Access 2007 help is appalling, isn't it. I've never been able to find anything in office help since 2002...

Anyway, the code you are looking for is

txtS.Value = lstS.Value

the value of the listbox is the value of the bound column in the currently selected row (at the risk of guessing, if you have a multiselect list with more than 1 item selected you probably get a comma-separated list of the selected items). If you have a multi-column listbox and want to use a value other than the bound column (although why you'd do that is beyond me ) you'd need

txtS.Value = lstS.List(lstS.ListIndex, columnindex)

where columnindex is the zero-based index of the column you're interested in. That example only works for non-multiselect lists though: for multiselects you'd need something like

Dim result as String, i as Integer

For i = 0 to lstS.ListCount - 1
    If lstS.Selected(i) result = result & lstS.List(i, columnindex) & ", "
Next i

txtS.Value = result

But that's probably enough showing off from me for tonight