I have a search form that contains a listbox that allows multiple selections to be selected and searched. My problem is that I want to add another listbox to the current code but cannot get it work along side the current listbox. See below code.
Code:
Private Sub Search_Click()
'Update the record source
Me.SearchSubform.Form.RecordSource = "Select * From Search " & BuildFilter
'Requery the subform
Me.Form!SearchSubform.Form.Requery
End Sub
Private Function BuildFilter() As Variant
Dim varWhere As Variant
varWhere = Null 'Main Filter
'Check for LIKE Status
If Me.Status > "" Then
varWhere = varWhere & "[Status] LIKE '*" & Me.Status & "*' And "
End If
'Check for LIKE Last Name
If Me.LastName > "" Then
varWhere = varWhere & "[Last Name] Like '*" & Me.LastName & "*' And "
End If
'Check for LIKE First Name
If Me.FirstName > "" Then
varWhere = varWhere & "[First Name] LIKE '*" & Me.FirstName & "*' And "
End If
'Check for LIKE Account Number
If Me.AccountNumber > "" Then
varWhere = varWhere & "[Account Number] LIKE '*" & Me.AccountNumber & "*' And "
End If
'Check for LIKE Social Security Number
If Me.SocialSecurityNumber > "" Then
varWhere = varWhere & "[Social Security Number] LIKE '*" & Me.SocialSecurityNumber & "*' And "
End If
'Check for LIKE Entity Name
If Me.EntityName > "" Then
varWhere = varWhere & "[EntityName] LIKE '*" & Me.EntityName & "*' And "
End If
'Check for LIKE EIN
If Me.EIN > "" Then
varWhere = varWhere & "[EIN] LIKE '*" & Me.EIN & "*' And "
End If
'Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE" & varWhere
' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("Search")
If Me!Company.ItemsSelected.Count > 0 Then
For Each varItem In Me!Company.ItemsSelected
strCriteria = strCriteria & "[Account List].[Company Name] = " & Chr(34) & Me!Company.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "[Account List].[Company Name] Like '*'"
End If
strSQL = "SELECT * FROM [Account List] " & "WHERE " & strCriteria & ";"
qdf.SQL = strSQL
Set db = Nothing
Set qdf = Nothing
End Function