How do I...return non BoundColumn value from MultiSelect-Extended listbox using VBA?

[Alan Lane]

Hello world:

I'm using Access 2003. I have 2 listboxes. One is a single column.
The other has two columns. I can use Dev Ashish's code (thanks Dev!) from
the Access MVP Website to accumulate the values from the bound column of a
MultiSelect listbox, so that I can include them in a SQL query that will
then be the recordsource for a report. This gives me 2 of the 3 values I
need to put into the SQL query. However, I can't get the non bound column
value from the 2 column listbox. How do I do this? I know that there must
be a way. Thanks in advance for any help offered.

Alan

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

dim strName as string
strName = lst2ColumnListBox.Column(1,lst2ColumnListBox.ListI ndex)

Columns are zero-based therefore column 2 is indicated by .Column(1).
The selected row is indicated by the ListBox's .ListIndex property.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQcO6M4echKqOuFEgEQI8sQCdH6yKgGxfdiXvhN5zLglcGI hjoggAoKyT
FXptOsHrFtJvfHb4M901AKdW
=ETJE
-----END PGP SIGNATURE-----
Alan Lane wrote:

Hello world:

I'm using Access 2003. I have 2 listboxes. One is a single column.
The other has two columns. I can use Dev Ashish's code (thanks Dev!) from
the Access MVP Website to accumulate the values from the bound column of a
MultiSelect listbox, so that I can include them in a SQL query that will
then be the recordsource for a report. This gives me 2 of the 3 values I
need to put into the SQL query. However, I can't get the non bound column
value from the 2 column listbox. How do I do this? I know that there must
be a way. Thanks in advance for any help offered.

[Alan Lane]

Hi MG:

Many thanks! Your basic idea worked great! I had to tweak it a little
bit for my particular case. Here's what my successful code looks like:

Dim varItem As Variant
Dim strMarket As String
Dim strRegion As String
Dim strCriteria As String

strMarket = ""
strRegion = ""
strCriteria = ","

'--Load selections from MarketRegionNew listbox into variables
If m_bytMarketRegionNewFlag Then
For Each varItem In Me!lstMarketRegionNew.ItemsSelected
strMarket = strMarket & Me!lstMarketRegionNew.ItemData(varItem)
strRegion = strRegion & Me!lstMarketRegionNew.Column(2, varItem)
strMarket = strMarket & strCriteria
strRegion = strRegion & strCriteria
Next varItem
'~
MsgBox CStr(Left$(strMarket, Len(strMarket) - Len(strCriteria))) & vbCrLf &
_
CStr(Left$(strRegion, Len(strRegion) - Len(strCriteria)))
End If

My listbox actually has 3 columns -- AirportCode (hidden, boundcolumn),
MarketName, and RegionNameNew. When I run this routine and get the
MessageBox, it shows me the correct list of selected Markets and then
Regions from the listbox. Yay!

Alan

"MGFoster" <[e-mail]> wrote in message
news:5U*****************@newsread1.news.pas.earthl ink.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

dim strName as string
strName = lst2ColumnListBox.Column(1,lst2ColumnListBox.ListI ndex)

Columns are zero-based therefore column 2 is indicated by .Column(1).
The selected row is indicated by the ListBox's .ListIndex property.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQcO6M4echKqOuFEgEQI8sQCdH6yKgGxfdiXvhN5zLglcGI hjoggAoKyT
FXptOsHrFtJvfHb4M901AKdW
=ETJE
-----END PGP SIGNATURE-----
Alan Lane wrote:

Hello world:

I'm using Access 2003. I have 2 listboxes. One is a single column.
The other has two columns. I can use Dev Ashish's code (thanks Dev!)
from the Access MVP Website to accumulate the values from the bound
column of a MultiSelect listbox, so that I can include them in a SQL
query that will then be the recordsource for a report. This gives me 2
of the 3 values I need to put into the SQL query. However, I can't get
the non bound column value from the 2 column listbox. How do I do this?
I know that there must be a way. Thanks in advance for any help offered.