Applying a filter on a bound subform based on unbound main form

[underpaidadmin]

I am using Access 2002. I have an unbound main form and a bound subform in it. (I mean the main form contains multiple unbound controls, but the subform has a recordsource and has bound controls.)

Main Form: frmSearchIt
Sub-Form: sbfBrowse

What I have been trying to do is take the values entered in the unbound main form and filter the subform by those values. This is how I capture the information from the main form and try to filter the subform:

Private Sub btnSearch_Click()
Dim dbf As Database
Dim rst As DAO.Recordset
Dim strWhere As String

strWhere = "WHERE "

If Not IsNull(Me.txtCaseNum) Then
strWhere = strWhere & "tblCases.CaseNum LIKE '*" & Me.txtCaseNum & "*'"
End If
'----------
If Not IsNull(Me.txtFirstName) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblNames.FirstName LIKE '*" & Me.txtFirstName & "*'"
End If
'----------
If Not IsNull(Me.txtLastName) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblNames.LastName LIKE '*" & Me.txtLastName & "*'"
End If
'----------
If Not IsNull(Me.txtTIN) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblNames.TIN LIKE '*" & Me.txtTIN & "*'"
End If
'----------
If Not IsNull(Me.txtOldCaseNum) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCases.OldCaseNum LIKE '*" & Me.txtOldCaseNum & "*'"
End If
'----------
If Not IsNull(Me.txtCompany) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblNames.Company LIKE '*" & Me.txtCompany & "*'"
End If
'----------
If Not IsNull(Me.txtTracerNum) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblTracers.TracerNum LIKE '*" & Me.txtTracerNum & "*'"
End If
'----------
If IsDate(Me.txtOpenDateFrom) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCases.OpenDate >= " & "#" & Me.txtOpenDateFrom & "#"
End If
'----------
If IsDate(Me.txtOpenedDateTo) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCases.OpenDate <= " & "#" & Me.txtOpenedDateTo & "#"
End If
'----------
If IsDate(Me.txtCloseDateFrom) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCases.CloseDate >= " & "#" & Me.txtCloseDateFrom & "#"
End If
'----------
If IsDate(Me.txtCloseDateTo) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCases.CloseDate <= " & "#" & Me.txtCloseDateTo & "#"
End If
'----------

If Len(strWhere) < 8 Then
strWhere = ""
End If

strWhere = strWhere & " GROUP BY tblCases.CaseNum, tblCases.OldCaseNum, tblNames.FirstName, tblNames.LastName, tblNames.Company, tblCases.OpenDate, tblCases.CloseDate, tblTracers.TracerNum, tblNames.TIN"

'2 Debug lines below, delete when working
Debug.Print strWhere

If Not Me.FormFooter.Visible Then
            Me.FormFooter.Visible = True
            DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
    With Me.sbfBrowse.Form
    .Filter = "Mid(strWhere, 7)"
    .FilterOn = True
    End With
End Sub

 

[PHV]

I don't think you may use a GROUP BY clause in a Filter property.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[underpaidadmin]

Thank you. I actually noticed that a little after I posted it along with the "WHERE" that should not have been there. Perhaps you might know if

tblCases.CaseNum LIKE '*06-0002*'

is an appropriate filter sytax because I think that is my problem. I don't get any errors now but it is not filtering the subform at all.