I looked at your code an made a few changes.
The sub "btnSearch_Click()" was falling through to the error statement.
The function "BuildFilter" was being called twice. Once at the line "strBuiltFilter = BuildFilter" and again at "strSQL = "SELECT......"
I changed the check to see if there were entries in the text box controls.. (see code)
The last " AND " was not removed from the variable "varWHERE".
One thing that doesn't make sense to me is if you enter "USA" for Me.txtCountry, the criteria will be
Code:
[Country] like "USA"
There is no wild card, so this is the same as
Are you wanting to find any country that begins with the data in Me.txtCountry?
For example, if you entered "U" in Me.txtCountry and clicked the search button, would you want USA, United Kingdom, Uganda, UAE,...?
If so, then you would have to add the wild card:
(example)
Code:
varWhere = varWhere & "[Country] LIKE " & tmp & Me.txtCountry & "*" & tmp & " AND "
(I created test forms.. )
If the name of the subform is the same as the name of the subform
control, I got an error.
I changed the name of the subform
control to "
sfrmsubClients" and ... no errror

.
I would have different names for the subform and the subform
control.
Modified code:
Code:
Private Sub btnSearch_Click()
On Error GoTo Err_btnSearch_Click
Dim strSQL As String
' Dim x As String
Dim strBuiltFilter As String
strBuiltFilter = BuildFilter
If Len(strBuiltFilter) > 0 Then
strSQL = "SELECT * FROM qryClientData WHERE " & strBuiltFilter
Else
'no criteria entered
strSQL = "SELECT * FROM qryClientData"
End If
' Debug.Print strSQL
Me.frmsubClients.Form.RecordSource = strSQL
Exit_btnSearch_Click:
Exit Sub
Err_btnSearch_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_btnSearch_Click
End Sub
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
tmp = """"
varWhere = Null ' Main filter
'varColor = Null ' Subfilter used for colors
' Check for Country
If Len(Me.txtCountry) > 0 Then
varWhere = varWhere & "[Country] LIKE " & tmp & Me.txtCountry & tmp & " AND "
End If
' Check for Company
If Len(Me.txtCompany) > 0 Then
varWhere = varWhere & "[Company] LIKE " & tmp & Me.txtCompany & tmp & " AND "
End If
' Check for Geographic Region
If Len(Me.TxtGeographicRegion) > 0 Then
varWhere = varWhere & "[Geographic_Region] LIKE " & tmp & Me.TxtGeographicRegion & tmp & " AND "
End If
If Len(Trim(varWhere)) > 0 Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
BuildFilter = varWhere
End Function
(The
blue text is what I modified/added.....)