×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Two Multi-Select List Boxes

Two Multi-Select List Boxes

Two Multi-Select List Boxes

(OP)
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

RE: Two Multi-Select List Boxes

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.

CODE --> vba

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:
  • Name of the list box
  • Name of the field
  • Data type of the field
  • Do you need to apply the filter to both the current form and the report?

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

RE: Two Multi-Select List Boxes

"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.

RE: Two Multi-Select List Boxes

(OP)
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)


CODE --> vba

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 

RE: Two Multi-Select List Boxes

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


---- Andy

There is a great need for a sarcasm font.

RE: Two Multi-Select List Boxes

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

RE: Two Multi-Select List Boxes

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".

CODE --> vba

Function BuildIn(lboListBox As ListBox, _
        strFieldName As String, strDelim As String) As String
    'send in
    '      List box control object
    '      Name of the field
    '      Delimiter which is typically a single quote for text
    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
        'remove the last ", " and add the ")"
        strIn = Left(strIn, Len(strIn) - 2) & ") "
    End If
    BuildIn = strIn
End Function 

Then use your new function like:

CODE --> vba

Private Sub cmdRunReport_Click()
     Dim strSearch As String
     Dim Task As String
     strSearch = "1 = 1 "
	 
     ' this code assumes both Model and Color are text/strings
     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

RE: Two Multi-Select List Boxes

(OP)
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.

RE: Two Multi-Select List Boxes

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

RE: Two Multi-Select List Boxes

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close