Search on results from a previous search
Posted on 2000-04-23
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
.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 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….)