PRoblem with filterring form

[btrini10]

Private Sub Command88_Click()
On Error GoTo Err_Command88_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim strFilter As String
Dim strFilter1 As String
Dim strFilter2 As String
Dim strFilter3 As String
Dim strFilter4 As String
Dim strFilter5 As String
Dim strFilter6 As String
Dim varItem As Variant



'******************Search First Name**********************
For Each varItem In Me!FirstNamesearch.ItemsSelected
strFilter1 = strFilter1 & "[FirstName] = " & _
Me![FirstNamesearch].ItemData(varItem) & " OR "
Next
' the next bit of code will subtract out the last "OR"
If strFilter1 <> "" Then
strFilter1 = Left(strFilter1, Len(strFilter1) - 4)
Else: strFilter1 = "[FirstName] Like '*'"
End If
'******************Search Last Name**********************
For Each varItem In Me!LastNamesearch.ItemsSelected
strFilter2 = strFilter2 & "[LastName] = " & _
"'" & Me![LastNamesearch].ItemData(varItem) & "' OR "
Next
' the next bit of code will subtract out the last "OR"
If strFilter2 <> "" Then
strFilter2 = Left(strFilter2, Len(strFilter2) - 4)
Else: strFilter2 = "[LastName] Like '*'"
End If
'******************Search Title**********************
For Each varItem In Me!Titlesearch.ItemsSelected
strFilter3 = strFilter3 & "[Title] = " & _
"'" & Me![Titlesearch].ItemData(varItem) & "' OR "
Next
' the next bit of code will subtract out the last "OR"
If strFilter3 <> "" Then
strFilter3 = Left(strFilter3, Len(strFilter3) - 4)
Else: strFilter3 = "[Title] Like '*'"
End If
'******************Search Contact Type**********************
For Each varItem In Me!ContactTypesearch.ItemsSelected
strFilter4 = strFilter4 & "[ContactType] = " & _
"'" & Me![ContactTypesearch].ItemData(varItem) & "' OR "
Next
' the next bit of code will subtract out the last "OR"
If strFilter4 <> "" Then
strFilter4 = Left(strFilter4, Len(strFilter4) - 4)
Else: strFilter4 = "[ContactType] Like '*'"
End If
'******************Search Company Name**********************
For Each varItem In Me!CompanyNamesearch.ItemsSelected
strFilter5 = strFilter5 & "[CompanyName] = " & _
"'" & Me![CompanyNamesearch].ItemData(varItem) & "' OR "
Next
' the next bit of code will subtract out the last "OR"
If strFilter5 <> "" Then
strFilter5 = Left(strFilter5, Len(strFilter5) - 4)
Else: strFilter5 = "[CompanyName] Like '*'"
End If

'******************Search String Final**********************
strFilter = "(" & strFilter1 & ") AND (" & strFilter2 & ") AND (" & strFilter3 & ") AND (" & strFilter4 & ") AND (" & strFilter5 & ")"

Debug.Print strFilter
Debug.Print strFilter1
Debug.Print strFilter2
Debug.Print strFilter3
Debug.Print strFilter4
Debug.Print strFilter5


' now, open the form using strFilter to pass a string
' containing the needed contacts
stDocName = "Contacts"
DoCmd.ApplyFilter , strFilter


Exit_Command88_Click:
Exit Sub

Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click

End Sub

Above is code with which I am attempting to filter my form. I have a search form with the 5 criteria and based on the selection, I would like the Contact form to be opened and show the records matching the criteria selected. Whenever I run the above I get "Object doesn't support this property or method" Any assistance would be appreciated. Thanks

 

[CautionMP]

btrini10,
Missing a couple of quotes.

...
 '******************Search First Name**********************
 For Each varItem In Me!FirstNamesearch.ItemsSelected
   strFilter1 = strFilter1 & "[FirstName] = " & _
       [red]"'" & [/red]Me![FirstNamesearch].ItemData(varItem) & "[red]'[/red] OR "
 Next
 ' the next bit of code will subtract out the last "OR"
 If strFilter1 <> "" Then
        strFilter1 = Left(strFilter1, Len(strFilter1) - 4)
        Else: strFilter1 = "[FirstName] Like '*'"
 End If
...

You might also take a look at [tt]BuildCriteria[/tt]

strFilter1 = strFilter1 & BuildCriteria("[FirstName]", dbText, "=" & Me![FirstNamesearch].ItemData(varItem))

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)