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.
Code:
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