Slow forms buildup after filter with no records (1 Viewer)

Zakraket

Registered User.
Local time
Today, 22:31
Joined
Feb 19, 2013
Messages
61
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

I'm trying to find what causes this second slow repeating buildup, but so far no luck.

Anyone?
 

arnelgp

error reading drive A:
Local time
Tomorrow, 04:31
Joined
May 7, 2009
Messages
12,380
CF can slow down forms.
have you considered using Link Master/Child Fields for your subform?
 

Zakraket

Registered User.
Local time
Today, 22:31
Joined
Feb 19, 2013
Messages
61
What's CF? (probably something obvious).

The main form has no recordset so there's nothing to connect.

I could set a recordset as a workaround, but how would I connect Master/Child to a "[field] like '*value*' OR [field2] like '*value*' OR etc" situation?

Also, as I stated (but maybe not clearly described) as long as the filter returns records, there's nothing slow about the form/subform. Only when the subform does not display records (because not records match the filter) and I reset the filter, it becomes real slow after initially displaying the unfiltered dataset as fast as it normally does.
Why does it start rebuilding it over and over again and only then becomes really slow? (4, 5, 6 times, haven't really counted it)
 

Zakraket

Registered User.
Local time
Today, 22:31
Joined
Feb 19, 2013
Messages
61
omg, of course :rolleyes:

I removed all conditional formatting but it does not make a difference (also, with CF enabled nothing is wrong as long as de form displays records)
 

Mike Krailo

Active member
Local time
Today, 16:31
Joined
Mar 28, 2020
Messages
254
And did you put a stop in your code to see what exactly is going on when there are no records shown in subforms? Is this done in on Current event?
 

Zakraket

Registered User.
Local time
Today, 22:31
Joined
Feb 19, 2013
Messages
61
Yes

There is nothing in the Currentevent in both forms

In short:
subForm.Open()
me.recordsource = "select * from vwProjects Where ProjectID=" & MainForm!ProjectID


MainForm!txtSearch.AfterUpdate()
strFilter = 'dynamically get some fields and build a strFilter like "[field] like '*value*' OR [field2] like '*value*' OR etc
if not isnull(txtSearch)
subform.recordsource = "select * from vwProjects Where ProjectID=" & MainForm!ProjectID & " AND " & strFilter
else
subform.recordsource = "select * from vwProjects Where ProjectID=" & MainForm!ProjectID
Endif


I tried some other things and the situation that I now have is that the recordsource of the subform is set incorrectly, so after filtering the subform shows #name in every field (aka it shows "one record with errored fields"), and when I now clear the filter it resets the recordsource but does NOT repeatedly rebuild.

So, if I would create a faulty recordset WHEN there are no results, resulting in #name in every field, seems to look like the problem would be fixed. However, counting the records before setting the recordsource will probably result in unnessecary slowdown, but it's worth a try

edit: i converted the view to a local table and now the problem is also gone.... So, SQL related, eventhough the view opens fine. Something about Access that can't handle this, problably something to do with the inefficient way Access handles datatraffic
I think I need to have a way to create a smaller dataset on server before Access calls the view (it needs to be a view since users need to edit data), because apparently Access can handle the data fine but only NOT after the form initially displays no record (regardless why no records are display, filter, recordsource)

I have more clients that work in similar matter but on other SQL databases (large and small) but never was this a problem.

edit2: now I reduced the amount of records in the view from 10000 (all projects) to 90 (filtered on projectID on SQL server) but the problem persists...
 
Last edited:

Zakraket

Registered User.
Local time
Today, 22:31
Joined
Feb 19, 2013
Messages
61
So after some testing, my final solution for this problem:

Code:
strSQL = "select from vwProject WHERE 'some filterstring'"
set rst = currentdb.openrecordset(strSQL)
rst.movelast
if rst.recordcount = 0
   subform.recordsource = ""
else
   subform.recordsource = strSQL
endif


This results in a subform with "#name" fields, but since there are no records found it's not even that weird... The recordcount doesn't really slow it down (even without prefiltering on SQL server), and after clearing the filterfield it only rebuilds once (and fast)
 

Users who are viewing this thread

Top Bottom