CraigEng
asked on
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
.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….)
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….)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks wsh2 for taking to the time to look at my question.
CraigEng
CraigEng
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.
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.
ASKER