Two Multi-Select List Boxes 1

[mtownbound]

So I have one multi-select list box that works fine, but I need to add a second field. I added the second string but my "And" statement wasn't working.

Here's my original, so where do I need to add the code for the second field(the message box is just for testing):

Private Sub cmdRunReport_Click()
Dim varItem As Variant
Dim strSearch As String
Dim Task As String

For Each varItem In Me.lboShoes.ItemsSelected
strSearch = strSearch & "," & Me.Shoes.ItemData(varItem)
Next varItem
MsgBox (strSearch)
If Len(strSearch) = 0 Then
Task = "select * from qryShoesinInventory"
Else
strSearch = Right(strSearch, Len(strSearch) - 1)
Task = "select * from qryShoesinInventory where ([Model] in (" & strSearch & "))"
End If
DoCmd.ApplyFilter Task
DoCmd.OpenReport "rptShoesinInventory", acViewPreview, , "Model IN(" & strSearch & ")"
End Sub

 

[dhookom]

Please use the TGML Code tag when posting code. It is much easier for us to help if we don't need to organize your code prior to figuring out your issue.

Private Sub cmdRunReport_Click()
    Dim varItem As Variant
    Dim strSearch As String
    Dim Task As String

    For Each varItem In Me.lboShoes.ItemsSelected
        strSearch = strSearch & "," & Me.Shoes.ItemData(varItem)
    Next varItem
    MsgBox (strSearch)

    If Len(strSearch) = 0 Then
        Task = "select * from qryShoesinInventory"
     Else
        strSearch = Right(strSearch, Len(strSearch) - 1)
        Task = "select * from qryShoesinInventory where ([Model] in (" & strSearch & "))"
    End If
    DoCmd.ApplyFilter Task
    DoCmd.OpenReport "rptShoesinInventory", acViewPreview, , "Model IN(" & strSearch & ")"
End Sub

Apparently the first list box is based on the Model field and it is numeric. Can we assume the other list box is also a text field? What are:
[ul]
[li]Name of the list box[/li]
[li]Name of the field[/li]
[li]Data type of the field[/li]
[li]Do you need to apply the filter to both the current form and the report?[/li]
[/ul]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

I added the second string (which string is that?) but my "And" statement wasn't working. "
Which "Add" statement is it?


---- Andy

There is a great need for a sarcasm font.

 

[mtownbound]

Apologies for the lack of clarity.

The form frmShoeSelection has one list box, based on the [Model] field, that opens the report with selected shoe models. Now I need to add a second list box to the form, based on [Color]. The filter needs to be applied to the form frmShoeSelection and the report rptShoesinInventory.

Query: qryShoesinInventory
Form: frmShoeSelection
Report: rptShoesinInventory
List Boxes (Both are text fields):
lboModel = Model (& ModelID)
lboColor = Color (& ColorID)

Private Sub cmdRunReport_Click()
     Dim varItem As Variant
     Dim strSearch As String
     Dim Task As String

     For Each varItem In Me.lboShoes.ItemsSelected
           strSearch = strSearch & "," & Me.Shoes.ItemData(varItem)
     Next varItem
     MsgBox (strSearch)
     
     If Len(strSearch) = 0 Then
          Task = "select * from qryShoesinInventory"
     Else
          strSearch = Right(strSearch, Len(strSearch) - 1)
          Task = "select * from qryShoesinInventory where ([Model] in (" & strSearch & "))"
     End If
     DoCmd.ApplyFilter Task
     DoCmd.OpenReport "rptShoesinInventory", acViewPreview, , "Model IN(" & strSearch & ")"
End Sub

 

[Andrzejek]

Plus, it looks like you have another control (a listbox?) on your Form named either [tt]lboShoes[/tt] or [tt]Shoes[/tt]... Both names are used in your code.


---- Andy

There is a great need for a sarcasm font.

 

[mtownbound]

Oops, lboShoes, not Model. Model is the actual column name in the table.

 

[dhookom]

I don't understand how your code could have worked if Model is text since the strSearch treats it like a number.

First create a generic function for building your multi-select listbox criteria. Save this in a general module with the name "modControlCode".

Function BuildIn(lboListBox As ListBox, _
        strFieldName As String, strDelim As String) As String
[COLOR=#4E9A06]    'send in
    '      List box control object
    '      Name of the field
    '      Delimiter which is typically a single quote for text[/color]
    Dim strIn As String
    Dim varItem As Variant
        
    If lboListBox.ItemsSelected.Count > 0 Then
        strIn = " AND " & strFieldName & " In ("
        For Each varItem In lboListBox.ItemsSelected
            strIn = strIn & strDelim & lboListBox.ItemData(varItem) & strDelim & ", "
        Next
        [COLOR=#4E9A06]'remove the last ", " and add the ")"[/color]
        strIn = Left(strIn, Len(strIn) - 2) & ") "
    End If
    BuildIn = strIn
End Function

Then use your new function like:

Private Sub cmdRunReport_Click()
     Dim strSearch As String
     Dim Task As String
     strSearch = "1 = 1 "
	[COLOR=#4E9A06] 
     ' this code assumes both Model and Color are text/strings[/color]
     strSearch = strSearch & BuildIn(me.lboShoes, "Model","'")
     strSearch = strSearch & BuildIn(Me.lboColor, "Color", "'")
     MsgBox (strSearch)
     Task = "select * from qryShoesinInventory where " & strSearch
     DoCmd.ApplyFilter Task
     DoCmd.OpenReport "rptShoesinInventory", acViewPreview, , strSearch
End Sub

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[mtownbound]

Thanks. Created both and when I run it, the message box gives me " 1=1 AND ColorID In ('54') AND ModelID In ('13') which let's me know it's pulling the right items, but then I get a Data Type Mismatch error for the ApplyFilter Task.

 

[mtownbound]

I figured it out, simple quotation error. Thanks for your assistance!!

 

[dhookom]

I guess this was in error based on the code and the results:
List Boxes (Both are text fields):

Glad to hear you got this sorted out.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016