Access 2010: Obtaining selected order from a listbox without using a table

I'm using Access 2010 and have a list box for users to select fields for a query.
I would like to obtain the selections in the order that the user selected them but without utilizing a table.
Any ideas?
Who is Participating?
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.

Jeffrey CoachmanMIS LiasonCommented:
Can you post some details on your listbox, table, Field names....etc
Jeffrey CoachmanMIS LiasonCommented:
And display the results How? Where?
Jeffrey CoachmanMIS LiasonCommented:
This will get you started...It displays the results in a textbox on the form and in a messagebox

(presumes you listbox has only one (text column)

Dim ctl As Control
Dim varItem As Variant
Dim strList As String
    Set ctl = Me.lstNames
    For Each varItem In ctl.ItemsSelected
        strList = strList & ctl.ItemData(varItem) & ", "
    Next varItem

    Me.txtVals = strList
    MsgBox strList
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Jeffrey CoachmanMIS LiasonCommented:
...and you can add this to strip off the last comma :

strList = Left(strList, Len(strList) - 1)
Jeffrey CoachmanMIS LiasonCommented:
If your real question is how to list the selections:"in the order that the user selected them", ...then this is not possible, as far as I know

As you can see from the code, you must simply loop the selections.
The "Order" in which you select the values (ex.: 1,22,4,18,45,2) is not stored anywhere .
The loop simply starts at the top of the list and works it s way down...
Rey Obrero (Capricorn1)Commented:
yes, this is doable...
upload a copy of your db with the form..
GNOVAKAuthor Commented:
Here's a rudimentary sample.  The actual list box will have about 80-100 items.
I'm building a query off of this and will need to place it in a subreport (still another EE question to follow). I can handle it however it comes across.
GNOVAKAuthor Commented:
sample lstbox
Rey Obrero (Capricorn1)Commented:
where is the db? i suggest that you upload the actual form and tables you are using.
There are a variety of ways to put listbox selections into a query (SQL Statement) while maintaining the selected order without using any additional tables.

The easiest, I think is to provide the user with a pair of listboxes side-by side.  One listbox holds the list of field names; the other displays the items the user has selected.  Code in the double-click event of the first listbox adds to the list of selected items in the second listbox.  With this process, the order of the selections is automatically 'remembered', and the rowsource of the second listbox can easily be turned into a SQL Statement (query) with fields in the order selected by the user.

Here's a sample, using the following code in the double click event of the listbox containing the field names:

Private Sub lstFieldNames_DblClick(Cancel As Integer)
    Dim strSelected As String
    Dim strSQL As String
    strSelected = Me.lstSelected.RowSource
    strSelected = Me.lstSelected.RowSource & ";" & Me.lstFieldNames
    If Left(strSelected, 1) = ";" Then strSelected = Trim(Mid(strSelected, 2))
    Me.lstSelected.RowSource = strSelected
    Me.txtSQL = "SELECT [" & Replace(Me.lstSelected.RowSource, ";", "],[") & "] FROM SomeTable"
End Sub 

Open in new window


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
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.