Return column index of multi select listbox

Hi experts, I have a 5 column listbox
is there a way to detect what column the user has selected ?

I can see how to identify the selected row and if necessary return all the columns associated to that row, but if the user selects column 3 of a row, I just want to return the data from the selected column for the selected row

I can't seem to identify what column has been selected

many thanks
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
list box column index is based 0

me.listbox.column(0)  ' 1st column
me.listbox.column(1)  '  2nd column
me.listbox.column(2)  '  3rd column
me.listbox.column(3)  '  4th column
me.listbox.column(4)  '  5th column
DBDavegOwnerAuthor Commented:
Hi capricorn, yes I can refer to the column index manually
but if the user selects column 3 is there any way I can identify he selected column 3
at the moment all I can seem to capture is the row index that was selected
Jim RiddlesPrepress/OMS SpecialistCommented:
Can you post an example of your 5 column select box or reference a page?
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Rey Obrero (Capricorn1)Commented:
oh, maybe this is what you need

me.listbox.column(index, row)

can you explain this in detail
<but if the user selects column 3 is there any way I can identify he selected column 3>

"you select a row not a column"
DBDavegOwnerAuthor Commented:
hi capricorn, yes I see many examples that capture the row the user selected and returning the bound column
or returning more columns by referencing them as .column(0) or 1,2,3 etc

what I have managed to do so far is if the user selects a row then return all the data from all 5 columns
but what I would like to achieve is if the user selects a specific column then just return the data from that column, not the first column or all columns.

But I can't seem to capture the column index when I click one of the columns
does the list box only select rows and have no facility to record the column selected ?
Rey Obrero (Capricorn1)Commented:
like what I said, "you select a row not a column"
Gustav BrockCIOCommented:
> what I would like to achieve is if the user selects a specific column then just return the data from that column

As Rey has told you, that information is not available.
After selecting a row, you could copy each column to, say, five textboxes, then let the user click the one to select. Or similar.

DBDavegOwnerAuthor Commented:
Hi Gustav, yes thanks for your advice
I've spent the afternoon searching on the internet and looked at possible solutions like setting up separate synchronized list boxes, which would be similar to your suggestion but yours would be far easier to manage.

My plan therefore is to replace the list box with a datasheet, that way individual rows and columns can be selected, I'll set it up so that if the user selects the primary bound column it returns all the columns and if they select just 1 column then it just returns that.

It won't allow me to use a multi select list box, but I should be able to work around the multi select issue by building a return string from on click or on double click events.

Do you think there will be any issue replacing the list box with a datasheet ?
Jeffrey CoachmanMIS LiasonCommented:

replace the list box with a datasheet, that way individual rows and columns can be selected
It is still confusing that you are saying that you need to "Select" a Column
Once again, this is normally not possible.

You can "specify" a column in a few ways.
For example, in an aggregate function, you can specify a certain column:
    Dlookup("fldCity","tblAddress","CustomerID=" & Me.txtCustomerID)
...Here you have "specified" the fldCity "Field".  
In this example, you are retrieving the City for the listed Customer

So here you can build an interface to "List" the column names and select a column from a listbox. (set a listbox RowSorceType to: Field List, and set the RowSource to the name of the Table)
Then you can feed the column name to the Lookup:
For example:
Dlookup(Me.ColumnListBox,"tblAddress","CustomerID=" & Me.txtCustomerID)
So in this example, you have specified the column, and the Row.
I have attached a simple sample database to illustrate this.

If this is not what you need then, again, ...can you clearly explain your need here?
What are you ultimately needing to do?
Don't say "I want to select a column"

Please clearly state what your ultimate goal is here.
For example:
"I need to let the user "Specify" a column.  Then I want to specify a row (ProductID, CustomerID, ...ect).
Then I want to______________"

Gustav BrockCIOCommented:
> Do you think there will be any issue replacing the list box with a datasheet ?

No. I think it is a splendid idea, and the OnClick event of a field/column will tell you which one was clicked.

As Jeff, I don't know your purpose by this, but neither do I have to; I guess it fills a need in your application.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DBDavegOwnerAuthor Commented:
Hi experts sorry for not explaining my reasons for wanting the user to select a specific column out of a row, I thought I'd cut to the chase, but I should have included all the relevant information at the outset, apologies for that.

The user is building an invoice description from a table of pre-set actions that the service tech may have performed,
In some cases all the tasks have been performed, but sometimes only some of them have been done.
it's basically a method to build descriptive strings to form the body of an invoice.

I could have inserted all the columns and had the user delete the unwanted columns if that task description wasn't done,
but I wanted to give the user the ability to click the descriptions he wanted to use.

The first column of a row has a short description of the task performed, there can be up to 4 additional columns that have more detailed descriptions of what has been done.

column 1: "Counterweight replacement":
column 2: "The counterweight mechanism has insufficient weight to close this fusible link fire door. We suspect this has........"
column 3: "We propose to dismantle the counterweight assembly and replace the existing counterweight with...."
Column 4: "Due to the weight of this door and ancillary equipment, two men will be required to carry out this work."
Column 5: “We have sourced higher quality components for this door, due to the critical nature of its location”

In this example the door may not be so heavy as to require 2 men, so column 4 may not be required.
Column 5 is also optional depending on the location of the door.

The paragraphs can be much longer than the snippets I've displayed, but the concept is the same.

I was hoping to give my client the ability to select all columns by clicking the primary first column or just enter some of the columns by clicking on them.
Jeffrey CoachmanMIS LiasonCommented:
To me, this data should be stored in "Rows", ...not columns.
This would make this a more "Normalized" design...
Then this would be easy.
Rey Obrero (Capricorn1)Commented:
where are you getting these values? the rowsource of the listbox?

you can do a better selection using cascading combo boxes or a datasheet subform

take a look here
DBDavegOwnerAuthor Commented:
Yes it appears a datasheet is the best approach for me now that I see you cant select individual columns of a list box like I was trying to do.
Rey Obrero (Capricorn1)Commented:
where are you getting these values? the rowsource of the listbox?
DBDavegOwnerAuthor Commented:
Hi Rey, no the data is coming directly from a table, the only reason a list box is mentioned was my mistaken belief that I could capture what column on a list box the user clicked
DBDavegOwnerAuthor Commented:
Hi Jeffrey, at the moment each row starts with a short description telling the user what that row relates to and then up to 4 columns of optional comments that provide additional information on the work that was performed, all or part of these additional comments may be required for the invoice description.
All the columns on each row relate to each other, it's just that some off the columns contain optional comments that may not need to be used on the invoice.
DBDavegOwnerAuthor Commented:
Hi Rey, thanks for those links to cascading combo boxes and lists
it's always nice to find links to useful resource pages and sample code.

I don't think the cascading combo box is what I'm looking for for this specific question, but I enjoyed looking at  both sites.

I'm coming to the firm conclusion that my data is best represented in a datasheet sub form instead of a listbox, that way I can identify whichever column the user clicks on and capture just what the user clicks on.

I think I should share the points between Rey and Gustav as you both provided alternative ways to achieve what I needed and also pointed out my mistake in choosing a listbox in the first place.

Is this a satisfactory distribution of the points ?

Jeffrey CoachmanMIS LiasonCommented:
I'll leave this to the other Experts...
Gustav BrockCIOCommented:
No problem.

DBDavegOwnerAuthor Commented:
not currently possible to select which column of a list box is clicked by user
several alternatives accepted
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.