Performance slow down after apply a filter(change from 0 records to many records)

[huicho7]

I using access 2000, i have a split db BE/FE, my dbms is an oracle 9i
db. I have many forms with subforms, and I have linked PRIMARY and
Secondary fields of Forms/Subforms, i have listbox in form for make the
filter. The problem, occurs when I apply a filter (with a listbox) and
the result is no records(0 records) in subform, then when I select an
element of the listbox, and this return one or more records, the
subform is repainted many times, and the performance is slow down.

After make a filter with no records, and then make a filter with
records, subform is repainted many times, and performance slow down.

Well, I hope someone can help me .
Thanks.

 

[tina]

well, this is a shot in the dark - have you tried removing the filter
completely, and then reapplying it. for instance, if you're running the
filter action on the listbox's AfterUpdate event, then something like

With Me!SubformControlName.Form
.FilterOn = False
.Filter = "whatever the new filter parameter is"
.FilterOn = True
End With

if the issue is actually about multiple repainting of the screen, you might
try suppressing the Repaint until the filter is applied, and then forcing a
repaint afterward, as

Application.Echo False
' whatever code runs to filter the subform
Application.Echo True
Me.Repaint

hth

 

[david epsom dot com dot au]

Are you doing this in code? If so, try this:

Each time you apply a filter, do it twice. Repeat
the code so that the filter is applied twice.

Each time you apply a filter, do it twice. Repeat
the code so that the filter is applied twice.

(david)

 

[huicho]

tina, thanks for your answers.

I'm doing filters using one property of the forms and subforms, "Link
child field" and "Link Master Field". With this properties, you not put
code in your form.
For example, if you have a table Days(monday, ....,sunday) and other
with your activities(Monday -- play soccer, .... sunday -- Do
homework), and you make a form with a listbox with days, and put into
form a Subform with your activities.
If you put on Subform Properties "Link child field" the field
"DAY"(because in Activities there is a field with this name) and in
"Link Master Field" you put a Control name cmboDays(cmboDays have list
of Days), if you do it access make filters automagicaly, when you
choose Monday, the subform is filtered by all records with these day,
if you choose Sunday is the same.

My problem consist that when I choose a day in the combobox that not
have records, and then choose a day with records, the records are
repainted many times, and the performance slow down. When pass from
zero records to "with erecords" the list is repainted many times and
slowly.


Well, thanks for all.

 

[david epsom dot com dot au]

I think that it may be repainting because of the way
it is doing the query: I think it may be possible that
the records are being returned one at a time (or a few
at a time), instead of as one group. You would be
able to see these by watching what request Oracle
is getting.

The "fix" would be to force a requery instead of an
update. In your case, there does not seem to be any
obvious good way to do that, but I would be trying
something like re-setting the subform recordsource
in the cbo After Update event:

me.sbf.form.recordsource = me.sbf.form.recordsource
me.sbf.form.recordsource = me.sbf.form.recordsource

(david)

 

[huicho]

Thanks david, It worked. Other solution was resetting subform
"sourceobject" property.

Thanks a lot.

 

[david epsom dot com dot au]

I'm glad that worked, and very pleased that you
replied with the information. That is an (almost)
new 'fix', (to possibly an almost new problem),
and I don't think anybody here really knew if
it was going to help.

So my thanks back to you as well,

(david)