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?
mbizupConnect With a Mentor Commented:
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

Jeffrey CoachmanMIS LiasonCommented:
Can you post some details on your listbox, table, Field names....etc
Jeffrey CoachmanMIS LiasonCommented:
And display the results How? Where?
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.