Search Form

[cbrsix]

Here is a picture of my search form in detail view...

It has a subform that is run through a query that is shown in the main form in table view. I want to select one or multiple companies (in this case) and have the results show in the subform table.

[cbrsix]

I found this link, but am not sure which one, if either, I should use and how to insert it properly into my code...

http://support.microsoft.com/kb/135546

[orange]

I think your decision to move away from multivalued fields is a good one.

Linq has 2007, perhaps he could look at the link you gave http://www.access-programmers.co.uk/...d.php?t=199778
and see if he can make any sense out of the multivalued field used as a filter.

[orange]

??? Where is the Listbox???

[cbrsix]

It's the big unbound box named Company1. As it stands right now, I have the property set to Multi Select option set to Extended. I am able to get it to work when only one item is selected, but not with more than one.

[orange]

see http://allenbrowne.com/ser-50.html and
http://allenbrowne.com/ser-62.html
http://fontstuff.com/access/acctut11.htm

Good luck with your project

[cbrsix]

thanks. i'll take a look at these

[cbrsix]

Those links were really helpful. I am able to get the listbox, when selecting multiple or single items, to work and show the appropriate data in my subform. However, click my Clear button, it comes back with an error with this line - strCriteria = Right(strCriteria, Len(strCriteria) - 1). Also, after I run the search from the listbox and then want to search, say, the LastName, it does not work I have determined that it is because the listbox puts in the query a criteria to search for the Company(ies) selected. I don't know how to clear that out...any suggestions? Here is my code.

Option Compare Database
Private Sub Clear_Click()
Dim intIndex As Integer
'clear all search items
Me.LastName = ""
Me.FirstName = ""
Me.AccountNumber = ""
Me.Company = ""
Me.SocialSecurityNumber = ""
Me.EntityName = ""
Me.EIN = ""
Me.Company1 = ""
End Sub

Private Sub Form_Load()
'clear the search form
Clear_Click
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 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 Company
If Me.Company > "" Then
    varWhere = varWhere & "[Company Name] LIKE '*" & Me.Company & "*' 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
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")
For Each varItem In Me!Company1.ItemsSelected
   strCriteria = strCriteria & ",'" & Me!Company1.ItemData(varItem) & "'"
Next varItem
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT * FROM [Account List] " & _
         "WHERE [Account List].[Company Name] IN(" & strCriteria & ");"
qdf.SQL = strSQL
Set db = Nothing
Set qdf = Nothing
BuildFilter = varWhere
End Function

[orange]

I'm not following this comment.

However, click my Clear button, it comes back with an error with this line - strCriteria = Right(strCriteria, Len(strCriteria) - 1).

strCriteria = Right(strCriteria, Len(strCriteria) - 1 is a statement within your Search_Click procedure

Can you save a copy of your database in mdb format--- I have access 2003 and can not open/use an accdb format database?

[cbrsix]

@orange - I hope this clears it up.

- I select an item(s) in the listbox and enter any other criteira I want and it returns exactly what I need.
- I then clear all fields and test just searching by LastName and it comes back with the following message
and when I click debug, it highlights the line of code specified

strCriteria = Right(strCriteria, Len(strCriteria) - 1)

[orange]

What happens if you comment that line?
You could put a Debug.Print strCriteria just before that line and see what the value is.





strCriteria

[cbrsix]

When you do the Debug.Print strCriteria, it puts this in the immediate window:
,'AIG Sun America Life Assurance Company','Allianz/Pimco Funds' (when I select two options from the listbox)
,'AIG Sun America Life Assurance Company' (when I select one option from the listbox)

[orange]

OK, so far so good.

Put a DEBUG.PRINT strSQL right before the qdf.sql liine and see if the SQL syntax is correct.

We're still working to solving the invalid function call, right?

[cbrsix]

SELECT * FROM [Account List] WHERE [Account List].[Company Name] IN('AIG Sun America Life Assurance Company');


Yes that is what we are still trying to solve, which occurs when I try and search just last name and nothing in the listbox.

[cbrsix]

I was able to figure out the invalid function call by adding an If statement. I have another problem though. I hope this is easier to solve.

Here is my current updated code.

Option Compare Database
Private Sub Clear_Click()
Dim intIndex As Integer
'clear all search items
Me.LastName = ""
Me.FirstName = ""
Me.AccountNumber = ""
Me.Company = ""
Me.SocialSecurityNumber = ""
Me.EntityName = ""
Me.EIN = ""
Me.Company1 = ""
End Sub
 
Private Sub Form_Load()
'clear the search form
Clear_Click
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 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 Company
If Me.Company > "" Then
    varWhere = varWhere & "[Company Name] LIKE '*" & Me.Company & "*' 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
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!Company1.ItemsSelected.Count > 0 Then
For Each varItem In Me!Company1.ItemsSelected
   strCriteria = strCriteria & ",'" & Me!Company1.ItemData(varItem) & "'"
Next varItem
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT * FROM [Account List] " & _
         "WHERE [Account List].[Company Name] IN(" & strCriteria & ");"
qdf.SQL = strSQL
Set db = Nothing
Set qdf = Nothing
Else
strCriteria = "[Account List].[Company Name] Like '*'"
End If
BuildFilter = varWhere
End Function

My problem now is that once I select an item in the list box, that criteria is not removed after I hit my clear button. If I search by last name, it will only find it if it is in the same selection as that company. What should I add to my clear button?