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

GNOVAK
GNOVAK used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Can you post some details on your listbox, table, Field names....etc
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
And display the results How? Where?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
...and you can add this to strip off the last comma :

strList = Left(strList, Len(strList) - 1)
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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...
Top Expert 2016

Commented:
yes, this is doable...
upload a copy of your db with the form..

Author

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.

Author

Commented:
sample lstbox
Top Expert 2016

Commented:
where is the db? i suggest that you upload the actual form and tables you are using.
Nerd
Most Valuable Expert 2012
Top Expert 2013
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:

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial