By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 1,327 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
-----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.


Nov 13 '05 #2

P: n/a
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" <me@privacy.com> 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.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.