Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Search on results from a previous search

Posted on 2000-04-23
Medium Priority
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 300 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

705 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