refresh recordset (1 Viewer)

[Zakraket]

For a database (Accessclient, SQL-backend) I'm creating a form with a searchfield. The form holds a listbox lstObjects that displays records (based on a rowsource SQL, not a recordset).
After finding the wanted record it needs to be edited by opening an editform. After editing records the listbox needs to display updated results

The search creates a SQL-statement that searches for the wanted value in all fields in the listbox (recordset), so the result of the search is:
[field1] like value or [field2] like value etc...

I can append this to the rowsource SQL of the listbox. The list is narrowed to the result and user can select/open the wanted record.

However, I need to create a search that narrows the results every time a search is done. So, the results of the first search should be searched to get a second (narrower) set of results and so on.

I would not know how to do this through creating a SQL statement (would become very long/complex/errorsensitive?) so I thought I would solve it like this:

Form-module:
Private rstUndo As DAO.Recordset

Private Sub Form_Load()
     Set rstUndo = Me.lstObjects.Recordset
End Sub

Public Sub Txt_Search_AfterUpdate()
    Call TestSearch(Me!txtSearch)
End Sub

Public Function TestSearch(varSearchvalue As Variant)

    Dim rstOld As DAO.Recordset
    Dim fld As DAO.Field
    Dim strFilter As String
    Set rstOld = Me.lstObjects.Recordset
    
    Set rstUndo = rstOld.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    
    For Each fld In rstOld.FIELDS
         'build filterstring like [field1] like value OR [field2] like value OR ... etc
    Next fld
    
    rstOld.Filter = strFilter
    Set Me.lstObjects.Recordset = rstOld.OpenRecordset(dbOpenDynaset, dbSeeChanges) 
    rstOld.Close
    Set rstOld = Nothing
    
End Function

So what happens is that after enering a searchvalue, the current recordset is parked in rstUndo (so that user can step back once to the last result when entering wrong search), a filterstring is built, a new recordset is opened based upon the filtered recordset and the listbox is set to the new recordset.
This can be repeated and the results are narrowed down every search.

The problem is however that I cannot get the listbox to refresh data anymore. If I change the underlying data the changes are never displayed in the listbox, except of course when I reopen the form.

If I "set me.lstObjects.recordset = me.lstObjects.recordset.openrecordset" (from msdn) it doens't work.
Adding dbOpenDynaset and dbSeechanges to the "recordset.openrecordset" statements doesn't help

Seems somewhere my recordsets become snapshots or something like that. Might be logical, but I have never used a recordset to fill a listbox before, so I'm not familiar with all the details of that. I was under the impression that by using DAO the recordsets are always "connected"

 

[arnelgp]

you dont set the recordset of a listbox, you use the rowsource, which is string.

 

[Zakraket]

you dont set the recordset of a listbox, you use the rowsource, which is string.

Why not, since it's possible? As I said, I've never used a recordset as datasource for a listbox, but in this case it's part of a solution (although it creates a new problem)

As I said, I need the user to be able to narrow down the records by searching multiple (as many as needed) times in all fields, and every time a searchvalue is entered the records need to be filtered. Since I expect this is difficult to handle by building a SQL-string I'm trying out different ways.



Another way could maybe be to create a new qryDef (not temporary), based on the previous qryDef, and so on, but then I'm creating an unspecified number of qryDefs that need to be cleaned up at some point.
So
search 1 creates qryDef1
search 2 creates qryDef2, based on qryDef1
search 3 creates qryDef3, based on qryDef2
etc

Or maybe build a qry/sql with a subquery with a subquery etc

 

[arnelgp]

you can still narrow your listbox using rowsource.
on load of the form save the original rowsource.
on your testsearch function you can build the rowsource by adding a Where clause.
test first if a 'where' clause is already in the rowsource, if not add it, ie:

if instr(strrowsource, ";")=len(strrowsource) then strrowsource=left(strrowsource,len(strrowsource)-1)
if instr(strRowSource, "where")= 0 then
strrowsource = strrowsource & " where "
end if
' code to append any another filter here
...
...
me.listbox.rowsource=strrowsource

'----------
the listbox will automatically recalculate since you change its rowsource.

 

[Zakraket]

Yes, that's what I did in the past, and it enables a user to enter one search, and every time the user does this the search will be done in all records.

What I need is:
listbox: 1000 records
search 1: 100 records left from 1000
search 2: 50 records left from 100 (so, find 50 records in 100 records, not 1000)
search 3: 10 records left from 50 (find 10 records in 50 records, not 1000)

Do you understand what I'm looking for? A search on a search on a search on a search etc.

edit: I now adjusted my code so that it will eventually generate a query with a subquery with a subquery etc (every subquery based on a specific searchvalue), but even with just one subquery it becomes quite slow already.
The method with the recordsets has no delay at all (just the refreshproblem)..

 

[MarkK]

you dont set the recordset of a listbox, you use the rowsource, which is string.

You absolutely can set the recordset of a listbox. It's a totally valid way to populate it with data.

 

[The_Doc_Man]

If your users can possibly want to do a search of some sort that eliminates things from consideration, be sure that you have indexes on the most commonly searched fields. Your "slowdown" problem should get better.

 

[Zakraket]

If your users can possibly want to do a search of some sort that eliminates things from consideration, be sure that you have indexes on the most commonly searched fields. Your "slowdown" problem should get better.

Good tip, I'll try that for sure

 

[Zakraket]

Anyone have thoughts on refreshing a recordset based on a recordset (based on a recordset etc)?

Or a different way to have a searchfunction that searchs in the "last" searchresults (on and on)

Have not found a solution for my problem yet

 

[jdraw]

Have you considered using AND between criteria

For Each fld In rstOld.FIELDS
'build filterstring like [field1] like value OR [field2] like value OR ... etc
Next fld

I'm not sure of your concern with

search 1: 100 records left from 1000
search 2: 50 records left from 100 (so, find 50 records in 100 records, not 1000)
search 3: 10 records left from 50 (find 10 records in 50 records, not 1000)

To me you want the result set eg, 100 or 50 or 10 depending on your criteria.

So adding to a where clause, or extending a filter seems appropriate.

Can you post a copy of the database? Remove anything confidential, compact and repair, then zip and post.

 

[Zakraket]

Have you considered using AND between criteria

Yes, maybe I'm mistaken, but the result of
select * from where [naam]="search1" and "search2" or [address]="search1" and "search2"

is not the same as

1) select * where [naam]="search1" and [address]="search1"
followed by
2) select * from where [naam]="search2" or [address]="search2" (on the resultset of the first select?

I'm not sure of your concern with

To me you want the result set eg, 100 or 50 or 10 depending on your criteria.

So adding to a where clause, or extending a filter seems appropriate.

Can you post a copy of the database? Remove anything confidential, compact and repair, then zip and post.

I'm trying to create a searchfunction with only one searchfield (searching in all fields) that narrows down the results.
This works when using recordsets as described but the refrehs is a problem.

I'll try to submit a small db that demonstrates

Maybe I should just forget refreshing the recordset, and "re-search" using the same subsequent values the user entered (thus rebuilding the recordset a few times). Would definitly be faster than the subquery approach

 

[jdraw]

zakraket,

Here is a set up by John Big Booty that is often referenced.

You might want to give it a try and set if it does/helps with what you need.

 

[Zakraket]

I have learned that

select * from tbl where [f1]="search1" or [f2]="search1"

followed by

select * from tbl where [f1]="search2" or [f2]="search2"

on the recordset of the first

Is the same as:

select * from tbl where ([f1]="search1" or [f2]="search1") and ([f1]="search2" or [f2]="search2"])

which I can fairly easy create with my existing code, so I will go with this solution. Thanks for your help

edit: I was starting to edit my SQL-function to have it build the above only to discover that I already included a function-parameter that does just this! Built this function years ago...

 

[jdraw]

Glad you have a workable solution!
Good luck with your project.