Slow forms buildup after clearing filter with no records

[Zakkie]

I have a MS Access client with a SQL database with linked tables and views.


In some forms I display records from a SQL view which works good. The form is a main form with a subform. Mainform does not have a recordsource, the subform has a recordsource which is a linked SQL view that is filtered. No performance issues.

So, the initiale subform.recordsource is like "select * from vw_Projects where Project_ID=" & form!projectID

I have created a Form!Search field on the main form which - through VBA - builds a filterstring for searching in (most) of the fields of the subform and combines it with the recordsource. This all works good, except when the filter has no results, i.e. the subform does not display a searchresult:

When I clear the searchfield after it displays no results, the subform is reset to the "unfiltered" recordsource, and then the following happens: the unfiltered recordsource is displayed (fast), then cleared, and then rebuilds really really slow multiple times. UI becomes unresponsive for at least 60sec while repeatedly rebuilding the records slowly, untill it is finished doing whatever it's doing

When I clear the searchfield after it has displayed a result (some records that match the filter), it only rebuilds once (fase)

Bit more detail:
The searchfield triggers a VBA function to build a filterstring and then set the form recordsource, like (simplified):
searchfield = "car"
-----> strFilter = " AND [item] like *" & form!searchfield & "*"

subform.recordsource = "select * from vw_Projects" & strFilter

- I've tried using a form.filter instead of a dynamic recordsource, but it's exactly the same.
- Also, some fields on the subform have Conditional formatting, but removing that also does not make a difference.
- I've also tried to see if something "double" is going on between the formevents which might cause the
- since the (linked) SQL view does not display performanceproblems - it's not blazing fast but good enough - I do not think it has anything to do with SQL server

The problem only occurs after resetting the filter after it has no results. I'm trying to find what causes this second slow repeating buildup, but so far no luck.

Anyone?

[Bob Fitz]

Cross-posted: https://www.access-programmers.co.uk...ecords.317099/ with replies

Please read: https://www.excelguru.ca/content.php?184

[Zakkie]

Yes. Are these forums related? I'm sorry, If so, my post can be closed

[Bob Fitz]

Yes. Are these forums related? I'm sorry, If so, my post can be closed

Whether they are related or not makes no difference to the etiquette of cross-posting, as you would understand if you had taken the trouble to read the link that I posted about the subject.