Search form with list boxes

[scottay]

G'Day all,



I have a form that I use as a search form. All is well right now, but I would like to change a few of my combo boxes to list boxes so the user can select a number of different criteria instead of one. I've been googling and have found some solutions to this; however, because my list boxes are part of group of search criteria, I am having trouble implementing it. Below I've posted the code for my search button. Sorry, it's fairly long.

The parts of the code I need help with are the ones related to [Type], [Category] and [Subcategory]. These are combo boxes now, but I'd like them to be List Boxes with multi select.

Note: I know how to convert the boxes and set properties, I just need help with the code side.

Private Sub cmdFilter_Click()
    Dim strWhere As String
    Dim lngLen As Long
    Dim varItem As Variant
    Dim strDelim As String
    Dim i As Variant
    Dim Criteria As String
    Const conJetDate = "\#mm\/dd\/yyyy\#"
    
    If Not IsNull(Me.txtFilterDWG) Then
        strWhere = strWhere & "([Drawing #] Like ""*" & Me.txtFilterDWG & "*"") AND "
    End If
    
    If Not IsNull(Me.txtFilterRev) Then
        strWhere = strWhere & "([Rev] Like ""*" & Me.txtFilterRev & "*"") AND "
    End If
    
    If Me.cbFilterOBS = True Then
        strWhere = strWhere & "([Rev] <> 'OBS') AND "
    End If
    
    If Not IsNull(Me.txtFilterStartDate) Then
        strWhere = strWhere & "([Date] >= " & Format(Me.txtFilterStartDate, conJetDate) & ") AND "
    End If
    
    If Not IsNull(Me.txtFilterEndDate) Then
        strWhere = strWhere & "([Date] <= " & Format(Me.txtFilterEndDate, conJetDate) & ") AND "
    End If
    
    If Not IsNull(Me.txtFilterDescription) Then
        strWhere = strWhere & "([Description] Like ""*" & Me.txtFilterDescription & "*"") AND "
    End If
    
    If Not IsNull(Me.txtFilterType) Then
        strWhere = strWhere & "([Type] Like ""*" & Me.txtFilterType & "*"") AND "
    End If
    
    If Not IsNull(Me.txtFilterCategory) Then
        strWhere = strWhere & "([Category] Like ""*" & Me.txtFilterCategory & "*"") AND "
    End If

    If Not IsNull(Me.txtFilterSubcategory) Then
        strWhere = strWhere & "([Subcategory] Like ""*" & Me.txtFilterSubcategory & "*"") AND "
    End If
    
    If Me.cboFilterCad = -1 Then
        strWhere = strWhere & "([Cad DWG] = True) AND "
    ElseIf Me.cboFilterCad = 0 Then
        strWhere = strWhere & "([Cad DWG] = False) AND "
    End If
    
    If Not IsNull(Me.txtFilterComments) Then
        strWhere = strWhere & "([Comments] Like ""*" & Me.txtFilterComments & "*"") AND "
    End If
    
    If Not IsNull(Me.txtFilterSize) Then
        strWhere = strWhere & "([Size] = """ & Me.txtFilterSize & """) AND "
    End If
    
    If Not IsNull(Me.txtFilterPN) Then
        strWhere = strWhere & "([Part Number] Like ""*" & Me.txtFilterPN & "*"") AND "
    End If
    
    If Me.cboFilterLinked = -1 Then
        strWhere = strWhere & "(Not IsNull([DWG_Filename])) AND "
    ElseIf Me.cboFilterLinked = 0 Then
        strWhere = strWhere & "(IsNull([DWG_Filename])) AND "
    End If
    
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "Nothing to do"
    Else
        strWhere = Left$(strWhere, lngLen)
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
        
End Sub

Thanks!
Scott

[pbaldy]

Here's one method:

http://www.baldyweb.com/multiselect.htm

You could adapt it to add to your filter string.

[scottay]

Thanks for your reply

That looks similar to what I've been trying to get to work before posting. Perhaps I need a little help getting it adapted...

I've changed the [Category] line to this:

    If Not IsNull(Me.txtFilterCategory) Then
        Set ctl = Me.txtFilterCategory
        For Each varItem In ctl.ItemsSelected
            strWhere = strWhere & ctl.ItemData(varItem) & ","
        Next varItem
    End If

I'm getting: Run-time error: '2448': You can't assign a value to this object.

[pbaldy]

On what line? FYI, a multiselect listbox will always be Null, so your test will never be met. You need to test ItemsSelected.Count as I did in my example. That will also not result in a valid string, because you're not adding the "FieldName IN(...) part.

[scottay]

I took out the first If statement.

2448 is highlighting "Me.Filter = strWhere"

Since I'm not opening a report, where should I put the FieldName IN? I'm just filtering a continuous form.

[pbaldy]

In my example, I'm building a string for the wherecondition argument of OpenReport. Both the wherecondition and filter are structured the same, as both are an SQL "WHERE" clause without the word "where". If you're getting an error there, I wonder if the string is valid. Add this line after the string is built but before it's used:

Debug.Print strWhere

That will print the finished string out to the VBA Immediate window. If you don't see the problem with it there, paste it here.

[scottay]

Hmm, sorry for a noob question, but where should I put Debug.Print strWhere?

It's built at the top with Dim strWhere As String, then it's used multiple times below, so I placed it between my Dim's and the first If statement... I got the same result as without it there.

[pbaldy]

Try between these two lines:

strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere

That would qualify for "after it's built but before it's used"

[scottay]

thanks.

Same result, though... still get the error. Is that suppose to bring up another window? Where does that print the debug?

Edit: Sorry, didn't have the intermediate window up... Here's what comes up there: Punch and

[pbaldy]

Does that help you find the problem? That is obviously an invalid string, which would look like:

FieldName = Value AND AnotherFieldName = SomeOtherValue

[scottay]

No, that really doesn't help. I did discover that this "Punch and" was referring to the selection I made, which was "Punch and Die." The lngLen at the bottom was chopping the rest off...

Also, is looking at this, how is it going to filter the form? It doesn't know which field to filter by. Ie. [Category] is what this needs to apply to. All of my others specify which field, but this one does not. Also, it needs to get combined with all the other selections that could possibly be made. I'm assuming that's what the AND at the end of all the other lines is for.

I wend ahead and attached the working database. Maybe this will help.This one does not yet have the Type, Category or Subcategory combo boxes converted to List Boxes yet. The Category one is the one I'd like to focus on now. I can adapt that to the other two. The form in question is the Search form.

[pbaldy]

Also, is looking at this, how is it going to filter the form? It doesn't know which field to filter by. Ie. [Category] is what this needs to apply to. All of my others specify which field, but this one does not.

That's what I've been trying to tell you. Try this (replacing what you had for category):

  Set ctl = Me.txtFilterCategory
  If ctl.ItemsSelected.Count > 0 Then
    strWhere = strWhere & "Category IN("
    For Each varItem In ctl.ItemsSelected
      strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
    Next varItem
    strWhere = Left(strWhere, Len(strWhere) - 1) 'get rid of trailing comma
    strWhere = strWhere & ") AND "
  End If
  '    If Not IsNull(Me.txtFilterCategory) Then
  '        'strWhere = strWhere & "([Category] Like ""*" & Me.txtFilterCategory & "*"") AND "
  '    End If

[scottay]

Sorry for the delayed reply. Quittin time at work

I pasted your code, but unfortunately still get the 2448. Looking at the immediate window, I get this: Category IN('Leads','MLC') AND. The pieces in the single quotes represent the choices I selected.

[pbaldy]

You must have changed something else, ie took out the part at the bottom that removed the last 5 characters. Leave everything as it was, and just replace the previous code for the category combo (I have it commented out above) with what I posted. I forgot you will also need this at the beginning:

Dim ctl As Control

[scottay]

Thanks for your reply. I had a feeling something else got tweaked, so I decided to go back to my fresh (working) database and apply the change there.

Success!

Thank you so much for your help!