unexpected requery of subform

[Wolfram Oldörp]

Hi group,

Access 2002, SQL-Server 2000

I have a mainform with a subform. Mainform is bound to a view for some order
data.
Subform is bound to a stored procedure. The parameter for the procedure are
changed in mainform current event.

On the mainform are comboboxes to choose an order. For this purpose the
allowedits is temporarily set to true, in gotfocus event of the combobox
(and false in lost focus). Allowedits only changes for the mainform, not for
the subform.

With profiler I can watch, that the adp requeries the procedure of the
subform, when allowedits of the mainform changes from false to true.
The procedure is a complex crosstab, so the user has to wait every time,
after he moves to one of the comboboxes.
Definitely this requery is not from VBA.
Setting the subform to snapshot does not change the behaviour.

How can we force the adp not to requery the subform ?

regards

Wolfram

 

[Sylvain Lafontaine]

Unecessary queries is an old problem for ADP. Try using an EXEC statement
instead; something like:

Subform.RecordSource = "EXEC myStoredProcedure parm1, parm2"

I have noticed in the past that ADP greatly diminush the number of
unecessary requeries when you are using EXEC statement instead of the name
of a SP but I don't know if this will help you.

You can also add a parameter somewhere that will short-circuit the building
of the crosstab.

 

[Wolfram Oldörp]

Hi Sylvain,

thanks for your advice. We already use "exec..." as recordsource.

I am wondering what you suggest as 'short-circuit'.
Do you mean to store the result in a table and only to run the crosstab
again, when different parameters are given?

Thank you

Wolfram

 

[Sylvain Lafontaine]

In your case, instead of caching, I was thinking more of adding a parameter
that would stop the calculation of the crosstab while the user is moving
from one combobox to the others.

Changing the record sources for the comboboxes (and list controls) to an
EXEC statement can also help in diminishing the number of unecessary
requests.

Also, I don't understand why your are changing the allowedit property.
Maybe you could keep it to be always ON and instead change the Enabled or
Locked properties of the individual controls?

Finally, I'm not sure from your description if these comboboxes are bound or
unbound. Personally, all comboboxes that I use for filtering and sorting
order are unbound and I never change the AllowEdit property of the form.

 

[Wolfram Oldörp]

Hi Sylvain,

please see comments below.

Thank you

Wolfram

In your case, instead of caching, I was thinking more of adding a
parameter that would stop the calculation of the crosstab while the user
is moving from one combobox to the others.

I am understanding now. We where thinking about a recordset unbound to the
server to popolate the subform. But we choose the way with lock/unlock the
combos.
....

Also, I don't understand why your are changing the allowedit property.
Maybe you could keep it to be always ON and instead change the Enabled or
Locked properties of the individual controls?

Just this we have done. The approach with allowEdits change comes from mdb
handling. While User is browsing records allowedits = false prevents him
from unwanted changes of data. When user clicks a "Change" Button we have
only to set allowedits = true instead of changing Locked to every control.
This principle comes from Mary Chipmans book and works fine in unbound mdb.

Finally, I'm not sure from your description if these comboboxes are bound
or unbound. Personally, all comboboxes that I use for filtering and
sorting order are unbound and I never change the AllowEdit property of the
form.

They are unbound
......