Search on results from a previous search

Posted on 2000-04-23
Last Modified: 2010-05-02
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….)
Question by:CraigEng
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 14

Accepted Solution

wsh2 earned 100 total points
ID: 2741665
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

Author Comment

ID: 2750818
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?

Author Comment

ID: 2761731
Thanks wsh2 for taking to the time to look at my question.

LVL 14

Expert Comment

ID: 2762288
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.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses
Course of the Month9 days, 3 hours left to enroll

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question