Solved

Search on results from a previous search

Posted on 2000-04-23
4
153 Views
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
        .Open
    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
rsPhotos.Requery

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….)
0
Comment
Question by:CraigEng
  • 2
  • 2
4 Comments
 
LVL 14

Accepted Solution

by:
wsh2 earned 100 total points
Comment Utility
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."
      Else
         ' 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 & _
            rstPublishersCountry.RecordCount
         MsgBox strMessage
      End If
      rstPublishersCountry.Close

   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.
   rstPublishers.MoveFirst
   Do While Not rstPublishers.EOF
      Debug.Print rstPublishers!pub_name & ", " & _
         rstPublishers!country
      rstPublishers.MoveNext
   Loop

   rstPublishers.Close

End Sub
0
 

Author Comment

by:CraigEng
Comment Utility
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?
0
 

Author Comment

by:CraigEng
Comment Utility
Thanks wsh2 for taking to the time to look at my question.

CraigEng
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Mysql vs Oracle 10 119
VBA filters 2 37
How does CurrentUser work? 10 25
Access Object Property from VBA Module in Excel 2010 2 18
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now