Get Multi-Select List Box as a single output

[andy_dyer]

Hi,

I've tried all possible searches both on this forum and on the wider web...

So apologies if anyone can point a way of wording this so it finds me a solution in under five minutes...

I have a list box lstDept that enables me to select multiple departments, I've got this working in feeding other queries to pull information based on people within the selected departments.

What I can't do and seems to be really obvious and should (I think) be really easy...

I just need what ever is selected to be within a query somehow with the full department names so that I can export to excel along with all my other queries to create a title so that people know what the data relates to...

Sorry if i've missed something really obvious...

 

[LPurvis]

Hi

It depends upon how you've implemented the using of a multi-select listbox as a source for limiting in your other queries.
Presumably it's through code one way or another - either creating an "In" list or filling a filtering table with values and joining to that table.

To acquire your list of values selected in the listbox purely for display the requirement is still for a function in VBA just as it would be for the "In" method - concatenating the results in the control.

For example a function like

Function fGetListVals(pclt As Access.Control, Optional pintColumn As Integer = 0, Optional pstrSep As String = ",")
    Dim intI As Integer
    Dim strList As String
 
    If Not TypeOf pclt Is Access.ListBox Then
        MsgBox "Passed control is not a listbox"
        Exit Function
    End If
 
    For intI = 0 To pclt.ListCount - intI
        If pclt.Selected(intI) Then
            fGetListVals = fGetListVals & pstrSep & pclt.Column(pintColumn, intI)
        End If
    Next intI
 
    fGetListVals = Mid(fGetListVals, Len(pstrSep) + 1)
End Function

Passing in the control and required column and separator.
That string can be used as a Like comparison to limit the results of a query or for display (if you really wanted).

If you're wanting something just some other data included in your filtered results then it's just a question of joining to the related tables surely?

Feel free to expand more on what you're wanting to see in detail.

 

[andy_dyer]

Private Sub cmdDeptReport_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryDeptSelect")

For Each varItem In Me!lstDept.ItemsSelected
   strCriteria = strCriteria & ",'" & Me!lstDept.ItemData(varItem) & "'"
Next varItem

If Len(strCriteria) = 0 Then
   MsgBox "You did not select anything from the list" _
          , vbExclamation, "Nothing to find!"
   Exit Sub
End If

strCriteria = Right(strCriteria, Len(strCriteria) - 1)

strSQL = "SELECT * FROM tblData " & _
         "WHERE tblData.[Department Name] IN(" & strCriteria & ");"

qdf.SQL = strSQL
Me.Refresh
DoCmd.RunMacro "RunDept"

Set db = Nothing
Set qdf = Nothing



End Sub

Then the query looks like this:

SELECT *
FROM tblData
WHERE (((tblData.[Department Name]) In ('2 Consultancy','4 Development','7 Technical Authoring','10 Project Management')));

This pulls all the data from my data table where the department(s) matches the list box and then I use this query to filter against in other queries...

What I can't get is the list of departments as a single result in a query...

 

[LPurvis]

The same list?
If you take the example function I mentioned earlier.... It's implementation could look something like:

SELECT *, fGetListVals(Forms!Formname!ListboxName) As YourValList
FROM tblData

If the data you wanted to display wasn't in the first column then you could have instead

SELECT *, fGetListVals(Forms!Formname!ListboxName, 1) As YourValList
FROM tblData

or to use a different delimiter

SELECT *, fGetListVals(Forms!Formname!ListboxName, 1, ";") As YourValList
FROM tblData

And so on.
That requires that the form is open for the function to resolve the values.
(You'd want some basic error handling to make sure).


However if you wanted to permanently alter the query's definition as you have in your existing method, then you could just change a single line of your function

strSQL = "SELECT *, '" & strCriteria & "' As ValList FROM tblData " & _
"WHERE tblData.[Department Name] IN(" & strCriteria & ");"

which would limit the rows and return the list.
Naturally - you could opt to only return the list instead

strSQL = "SELECT *, '" & strCriteria & "' As ValList FROM tblData"

Cheers!
(From South Yorkshire ;-)

 

[LPurvis]

Oh, and incase anyone's thinking I've dropped a.. mistake... and are wondering why I used the more explicit (and hence slightly less efficient) iterating through the entire ListCount items rather than the ItemsSelected collection, that's to pre-empt the possibility of running into a known listbox bug.

Cheers!

 

[andy_dyer]

Thanks Leigh,

I'm getting an error - I've saved the original function you kindly provided and am tryign to call it using this statement in my query...

SELECT *, fGetListVals([Forms]![frmQuery]![lstDept], 1) As YourValList
FROM tblDept;


tblData is where all my data is but my list of departments and the source for the list box is tblDept, so i guess I need that one instead...

The structure is DeptId and Dept Name and the bound column is the name...

The error I'm getting is:

"This expression is typed incorrectly, or is too complex to be evaluated."

Any ideas what i've done wrong?

 

[andy_dyer]

HELP!!!

I'm still really stuck with this - I've got a presentation to the whole company on Wednesday and I cannot work out for anything how to get the list of values out as one output in a query...

:-(

 

[LPurvis]

Hi.

Right. Let's get that sorted.
The function call will work as an expression.
However I can actually well imagine that the query engine isn't passing the form as an object.

We can switch the function around a bit to easily allow for that.

Function fGetListVals2(strForm As String, strControl As String, Optional pintColumn As Integer = 0, Optional pstrSep As String = ",")
    Dim intI As Integer
    Dim strList As String
    Dim ctl As Access.Control
    
    Set ctl = Forms(strForm)(strControl)
    If Not TypeOf ctl Is Access.ListBox Then
        MsgBox "Passed control is not a listbox"
        Exit Function
    End If
    
    For intI = 0 To ctl.ListCount - intI
        If ctl.Selected(intI) Then
            fGetListVals2 = fGetListVals2 & pstrSep & ctl.Column(pintColumn, intI)
        End If
    Next intI
    
    fGetListVals2 = Mid(fGetListVals2, Len(pstrSep) + 1)
End Function

And then your query would be
SELECT *, fGetListVals2("frmQuery", "lstDept", 1) As YourValList
FROM tblDept;

And the object isn't evaluated until within VBA then.

Good luck with the presentation!
Cheers.

 

[andy_dyer]

Hi Leigh - thanks for that it works!

I get all items selected in the listbox but repeated for all the lines in my tblDept...

Is there a way I can not display those?

Thanks again - feels so close!

 

[andy_dyer]

Done it!

SELECT fGetListVals2("frmQuery","lstDept",1) AS YourValList
FROM tblDept
GROUP BY fGetListVals2("frmQuery","lstDept",1);

Sorted!

Thank you ever so much - thought I was going crazy...

Whereabout in S Yorkshire are you? i live in Doncaster but work in Sheffield...

 

[DCrake]

Andy Check out this list box demo. You need to look at the last form in the list and see how it works.

Basically it resets the sql in your query to manage the Not In() and In() functions.

Hope this helps

David

 

[LPurvis]

Hi Andy. Glad you're sorted.

I live in Rotherham now - but am from the North East originally and work in... oohhh pretty much anywhere inbetween and elsewhere too. ;-)
Almost wherever I'm requested to basically.
(Mostly do so from home though - travelling around for onsite work when required).

It's a chore but somebody's gotta do it.

Cheers! (Good luck for tomorrow).