Search Form

[cbrsix]

I have 2 tables, ClientList and [Account List]. It is one client per multiple accounts. I have a search form currently and I changed a few things around and now I don't know how to get it to work. The LastName, FirstName used to be on the Account List but they are no longer there. My problem is that the BuildFilter calls just the Account List table to search in the query. How can I get it to search for the LastName in the ClientList?

Option Compare Database
Private Sub Clear_Click()
Dim intIndex As Integer
'clear all search items
Me.LastName = ""
Me.FirstName = ""
Me.AccountNumber = ""
Me.SocialSecurityNumber = ""
Me.EntityName = ""
Me.EIN = ""
Me.Company = ""
Me.Status = ""
Me.Representative = ""
'Update the record source
Me.SearchSubform.Form.RecordSource = "Select * From Search " & BuildFilter
'Requery the subform
Me.Form!SearchSubform.Form.Requery
End Sub
Private Sub Form_Close()
Clear_Click
'Update the record source
Me.SearchSubform.Form.RecordSource = "Select * From Search " & BuildFilter
'Requery the subform
Me.Form!SearchSubform.Form.Requery
End Sub
Private Sub Form_Load()
'clear the search form
Clear_Click
'Update the record source
Me.SearchSubform.Form.RecordSource = "Select * From Search " & BuildFilter
'Requery the subform
Me.Form!SearchSubform.Form.Requery
End Sub
Private Sub PreviewReport_Click()
'Open Search Report
DoCmd.OpenReport "SearchReport", acViewPreview
'Close Search Form
DoCmd.Close acForm, "Search"
End Sub
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 & "[LastName] Like '*" & Me.LastName & "*' And "
End If
'Check for LIKE First Name
If Me.FirstName > "" Then
    varWhere = varWhere & "[FirstName] 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 & "[SSN] 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 strCriteria1 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].CompanyName = " & Me!Company.ItemData(varItem) & "OR "
      Next varItem
      strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    Else
      strCriteria = "[Account List].CompanyName Like '*'"
   End If
   If Me!Representative.ItemsSelected.Count > 0 Then
      For Each varItem In Me!Representative.ItemsSelected
         strCriteria1 = strCriteria1 & "[Account List].[Reps] = " & Chr(34) & Me!Representative.ItemData(varItem) & Chr(34) & "Or "
      Next varItem
      strCriteria1 = Left(strCriteria1, Len(strCriteria1) - 3)
   Else
      strCriteria1 = "[Account List].[Reps] Like '*'"
   End If
   strSQL = "SELECT * FROM [Account List] " & "WHERE " & "(" & strCriteria & ") AND (" & strCriteria1 & ")" & ";"
   qdf.SQL = strSQL
   Set db = Nothing
   Set qdf = Nothing
End Function

[June7]

Options:

1. do the search on a query that joins ClientList and AccountList

2. don't search on individual name parts, build multi-column combobox that has ClientID and concatenated name parts - user selects name and code searches with ClientID

[cbrsix]

@June7

Options:

1. do the search on a query that joins ClientList and AccountList
- I have tried this numerous times and no matter what fields I put in the query and join the two tables, when I search, only the fields in the BuildFilter (CompanyName, Reps, and now the AccountNumber) show in the query. The query is no longer joined to the other table when I go to look at it. How can I add the other table to the strSQL to have it search that table as well?

2. don't search on individual name parts, build multi-column combobox that has ClientID and concatenated name parts - user selects name and code searches with ClientID
- As for this, I am going to utilize this on a different form. But for the purposes of this search form, I do want to be able to search on individual name parts.

[June7]

I just realized you are modifying QueryDef - why? Why not build a query object that joins tables and use that query as the RecordSource for form or report and apply filter criteria to form or report?

If you must modify QueryDef, use Query Builder to help you construct the sql statement that joins tables and then copy/paste into the VBA procedure. Something like:

SELECT *.[Account List], *.ClientList FROM ClientList INNER JOIN [Account List] ON ClientList.ClientID=[Account List].ClientID


BTW, advise not use spaces, special characters/punctuation (underscore is exception) in names, nor reserved words as names. If used must enclose in [].