Search on results from a previous search

I have a text box where the user can input a search string (preferably 1 word), which is used to return a selection of records.  I would like to allow users to type another word in the same text box (after the first search has returned results) to search only the records returned from the first search.  Here is what I’ve done so far…

Using VB6 sp3, ADO (2.1) data connection, datagrid.

Dim cnPhotos As ADODB.Connection
Dim rsPhotos As ADODB.Recordset

In the Form_Load event I have the following

Set cnPhoto = New Connection
    With cnPhotos
        .Provider = "Microsoft.Jet.OLEDB.3.51"
        .ConnectionString = "Data Source=C:\Test.mdb;"  ‘test.mdb is an Access97 database with a table called “Photos”, fields called photoID, Subject, Location
    End With

'Open a recordset
    Set rsPhotos = New ADODB.Recordset
    rsPhotos.CursorLocation = adUseClient
    rsPhotos.Open “select PhotoID,Subject,Location, from Photos, cnPhotos, adOpenKeyset

There is a text box (txtCriteria1) where the user can input a search string (preferably 1 word), the string is used in an SQL statement like this:

strSQL = "select PhotoID,Subject,Location, from Photos WHERE Subject LIKE ‘%” & txtCriteria1 & “%’ OR Location LIKE ‘%” & txtCriteria1 & “%’"  

I’ve included the % symbol for wildcards.

Then I put the SQL string to the recordset

rsPhotos.Close   ‘it didn’t like the .open statement below, unless I closed it first…seems odd, is this the correct way to do this?

rsPhotos.Open strSQL, cnPhotos, adOpenDynamic

This search works fine, but now, what I would like to do, is allow the user to type another word in  txtCriteria1, and have it search only in the results of the first query.  Presently, it returns results from the entire recordset.  I’ve thought about making a duplicate or clone recordset, and working with that one, (allowing records to be deleted), or creating a new table, and using .ADDNEW for all the records returned from the first query, then performing the second or subsequent query on the new recordset, or saving the first instance of txtCriteria1 in another variable, and using it again in another SQL statement.

I’ve tried looking under search, nested queries, filters, on MSDN cd’s, Mastering VB6 fundamentals, Microsoft VB site, and all the free VB code sites, and I can’t seem to find anything:  My conclusion is that I’m looking for the wrong thing, or else it’s not a very common task….

Does anyone have any suggestions? (to help with the problem….)
Who is Participating?
wsh2Connect With a Mentor Commented:
Use a Filter on the Recordset you have already downloaded. An Example from MSDN:

Filter and RecordCount Properties Example:

This example uses the Filter property to open a new Recordset based on a specified condition applied to an existing Recordset. It uses the RecordCount property to show the number of records in the two Recordsets. The FilterField function is required for this procedure to run.

Public Sub FilterX()

   Dim rstPublishers As ADODB.Recordset
   Dim rstPublishersCountry As ADODB.Recordset
   Dim strCnn As String
   Dim intPublisherCount As Integer
   Dim strCountry As String
   Dim strMessage As String

   ' Open recordset with data from Publishers table.
   strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   Set rstPublishers = New ADODB.Recordset
   rstPublishers.CursorType = adOpenStatic
   rstPublishers.Open "publishers", strCnn, , , adCmdTable

   ' Populate the Recordset.
   intPublisherCount = rstPublishers.RecordCount

   ' Get user input.
   strCountry = Trim(InputBox( _
      "Enter a country to filter on:"))

   If strCountry <> "" Then
      ' Open a filtered Recordset object.
      Set rstPublishersCountry = _
         FilterField(rstPublishers, "Country", strCountry)

      If rstPublishersCountry.RecordCount = 0 Then
         MsgBox "No publishers from that country."
         ' Print number of records for the original
         ' Recordset object and the filtered Recordset
         ' object.
         strMessage = "Orders in original recordset: " & _
            vbCr & intPublisherCount & vbCr & _
            "Orders in filtered recordset (Country = '" & _
            strCountry & "'): " & vbCr & _
         MsgBox strMessage
      End If

   End If

End Sub

Public Function FilterField(rstTemp As ADODB.Recordset, _
   strField As String, strFilter As String) As ADODB.Recordset

   ' Set a filter on the specified Recordset object and then
   ' open a new Recordset object.
   rstTemp.Filter = strField & " = '" & strFilter & "'"
   Set FilterField = rstTemp

End Function

Note   When you know the data you want to select, it's usually more efficient to open a Recordset with an SQL statement. This example shows how you can create just one Recordset and obtain records from a particular country.

Public Sub FilterX2()

   Dim rstPublishers As ADODB.Recordset
   Dim strCnn As String

   ' Open recordset with data from Publishers table.
   strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   Set rstPublishers = New ADODB.Recordset
   rstPublishers.CursorType = adOpenStatic
   rstPublishers.Open "SELECT * FROM publishers " & _
      "WHERE Country = 'USA'", strCnn, , , adCmdText
   ' Print current data in recordset.
   Do While Not rstPublishers.EOF
      Debug.Print rstPublishers!pub_name & ", " & _


End Sub
CraigEngAuthor Commented:
Thanks wsh2 for suggesting the FilterField function.  I had looked at that and didn't see the connection to what I wanted to do.  I have now tried it and it does work, however, I was hoping that I could re-search on multiple fields (in my example, the subject and location), and then perform the re-search multiple times.  The example given allows a search on one field only, and returns the recordset into a new recordset.  I can't quite figure out how to use the second recordset to do a third search, then the third to do a fourth search, and so on and so on....  Any suggestions?
CraigEngAuthor Commented:
Thanks wsh2 for taking to the time to look at my question.

Craig.. I am caught up on a work problem.. <sigh>. Real quick though.. you want to build up either your SQL statement or Find statement.

Load your criterea into a array (Eg. Level(0).. Level(1).. then iterate through the array to build a Search string to do your SQL or Find. Eg.

For lngIndex = 0 to Ubound(MyArray)
   SQL = SQL _
      & "([A] = "" & Level(lngIndex) & """ " _
      & "[B] = "" & Level(lngIndex) & """ " _
      & "[C] = "" & Level(lngIndex) & """) " _
      & " AND "
Next lngIndex

SQL = Left(SQL, len(SQL) - 5) ' Remove the last " AND "

This will give you search within a search capability.

I know this is sketchy.. but I am pressed for time.. I hope you can pick up the idea from here tho.. <smile>.

PSsssst.. will have more time later this week.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.