Search form and Result form

[underpaidadmin]

I have been trying to do this for over a week and I just can't make it work. I have an unbound form that users enter searchable criteria into. Behind that form I have VB code to grab all that info and construct it into a query. I have another form which I want to display the results. I have seen almost exactly what I want to do in another template Access database called "Issues Database" and have unsuccessfully tried to morph it into what I think I need. The search form, frmSearchIt, has the following code behind it:

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

strWhere = "WHERE "
strSQL = "SELECT tblCases.CaseNum, tblCases.OldCaseNum, tblNames.FirstName, tblNames.LastName,tblNames.Company , tblCases.OpenDate, tblCases.CloseDate, tblTracers.TracerNum, tblNames.TIN FROM (tblCases LEFT JOIN tblNames ON tblCases.CaseNum = tblNames.CaseNum) LEFT JOIN tblTracers ON tblCases.CaseNum = tblTracers.CaseNum "

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"
strSQL = strSQL & strWhere & ";"

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

If Not Me.FormFooter.Visible Then
            Me.FormFooter.Visible = True
            DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
        Me.sbfBrowse.Form.Filter = strWhere
        Me.sbfBrowse.Form.FilterOn = True
    
Set dbf = CurrentDb
Set rst = dbf.OpenRecordset(strSQL)
End Sub

The form I use to display results,sbfBrowse, is used in many places to display some information of records of several different tables linked by [CaseNum]. Right now all I have as code under sbfBrowse is:

Public Sub ShowResults(ByVal RS As ADODB.Recordset)
  ' put code to show the data
  Call Me.Show(vbModal)
End Sub

Which I don't really understand how it should work. As far as I can tell I shouldn't need to use a recordset to do what I want to do. I would appreciate any headway anyone could give me.

 

[PHV]

Replace this:
Me.sbfBrowse.Form.Filter = strWhere
with this:
Me.sbfBrowse.Form.Filter = Mid(strWhere, 7)

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 for the extremely fast response. The problem is I am getting a Run-time error message, "You can't assign a value to this object." Below is the select query I use as the record source:

SELECT tblCasesMain.*, IIf([CaseStatus]<>"OpenThis","") AS OpenThis
FROM tblCasesMain
ORDER BY tblCasesMain.OpenDate, tblCasesMain.CaseNum;