Subform does not show results from a multi-select listbox

[yellowgelb]

Hi,
I have created a unbounded multi-select list box in the form header called lstSpecies. In the detail of the form I would like to have a sub-form as a datasheet with different columns.
I used the following code to select the names from the list box. The code works fine but it doesn't show me the selected names in the datasheet as a sub form. When I use the only fields from the fieldlist in the detail to show me the results the code works fine. So I added the red marked lines (see below), which I used for other parts of the same form, to see the result also in a datasheet subform. But it doesn't work. I don't get an error. Regardless of which name I choose from my lstSpecies list I always get the same 16 rows as a result, which not necessarily contain the chosen name. I don't understand that.

I hope very much someone can help me to sort that out. Thanks a lot!! Anje

[Private Sub cmdFilter_Click1()]
[Dim strWhere As String]
[Dim varItem As Variant]
[Dim lngLen As Long]


[With Me.lstSpecies]
[For Each varItem In .ItemsSelected]
[If Not IsNull(varItem) Then]
[strWhere = strWhere & """" & .ItemData(varItem) & """, "]
[End If]
[Next varItem]
[End With]

[lngLen = Len(strWhere) - 2 'Without trailing comma and space.]


[If lngLen > 0 Then]
[strWhere = "[CatSpeSpeciesRef] IN (" & Left$(strWhere, lngLen) & ")"]
[End If]

[With Me!sfmBasicQuery.Form]
[Me.Filter = strWhere]
[Me.FilterOn = True]


[End With]


[End Sub]

[orange]

You do NOT need all the square brackets "[" "]".
You use [ and ] around variable and field names containing special characters and/or embedded spaces

Try putting

Debug.Print strWhere
in this

 If lngLen > 0 Then 
  strWhere = "[CatSpeSpeciesRef] IN (" & Left$(strWhere, lngLen) & ")" 
  Debug.Print  strWhere
  End If

It should show you the value of strWhere in the immediate window

[yellowgelb]

Thanks a lot for your answer!
Ignore the square brackets []. I don't have them around my code within Access. I have used them only here in the forum, because I thought I have to mark my code like that. (...."Use code tags to enclose your code: [ code]Your code here...[/code]."...) Now I know how I have to do that. Sorry.I used the Debug.Print command. That is an example for an expression I get in the immediate window: [CatSpeSpeciesRef] IN ("Australian magpie", "Australian pratincole")

The code filters the correct results, when I use just simple fields as an output (without a subform). But do you have an idea, what I have to change within the code to get the same correct results also in a subform (datasheet). I added the red marked rows, which I also use for other parts of the form. But apparently that is not working with this code for multi-select list boxes.

Thanks a lot for your help!

[ssanfu]

I've been looking at your code and thinking about it. My first thought is that the code is executed from a button click event. But I have never seen the click event have a "1" at the end...

Private Sub cmdFilter_Click1()

But you say the code works.



So looking at the code this morning, it occured to me you are not setting the filter for the subform, you are setting the filter for the main form.

    With Me!sfmBasicQuery.Form   '<= this should refer to the sub form
        Me.Filter = strWhere      '<= this (Me) refers to the main form
        Me.FilterOn = True        '<= this (Me) refers to the main form
    End With

You might try:

    With Me!sfmBasicQuery.Form
        .Filter = strWhere
        .FilterOn = True
    End With

or

    With Me!sfmBasicQuery.Form
        !Filter = strWhere
        !FilterOn = True
    End With

I don't use subforms like this to remember the correct syntax.

[orange]

The debug.print Yourvariable
is a common debugging/checking technique to see What is actually rendered to be used by Access

You will see incorrect syntax, or if you take a complete SQL query and debug.print it
You can copy the rendered sql from the immediate window and put it in the query wizard and
execute it. If it works good, you know the code is good. If you get a syntax error, you know what to fix (usually).

[yellowgelb]

Thanks a lot for your help! It is working without the Me and just an dot. Great. There is still a lot to learn. Also thanks to orange for your quick reply and the advice.