Subform Requerying

[RJ]

Is it “normal†when updating a main form that the subforms get automatically
re-queried?

I was looking at the Profiler and I am seeing a requery for all my subforms
whenever I update the main form. The updated field has nothing to do with
the subform linkage and I am not calling a requery. It would be nice not to
have this increase in network traffic and roundtrips to the server when it is
totally unnecessary!

I am defining the linkage in the main form properties (as an example) in the
usual way

Link Child Fields: EC_FunctionId
Link Master Fields: EM_FunctionId

In the subforms
Input Parameters: EM_FunctionID

Am I doing something wrong or is this just normal? Is there anything I can
do to prevent this redundant requerying? I know the interface to SQL Server
from ADP's is chatty but that seems a bit of overkill.

Thanks for your comments!

 

[Sylvain Lafontaine]

The subform is requeried each time because you have defined the Link Child
and Link Master fields. You don't say what type of RecordSource you are
using for the subform so I cannot tell you more on this.

Also, you don't describe exactly what you mean by "updating the main form".
Are you navigating to another record with another value for
EC_FunctionID/EM_FunctionID or if you are staying on the same record?

 

[Sylvain Lafontaine]

I forgot to add that is you are using a SP procedure for the subform, you
should replace it's name with a call to an EXEC statement.:

Me.SubformControl.Form.RecordSource = "EXEC MySP param1, param2, ...."

and for a table replace it with a call to a select query:

= "Select a, b, c from table1 where ..."

Don't forget that this won't work if you forgot to remove the Link Child and
Link Master fields as well as any Server Filter. In fact, in the case of
the EXEC statement, you will even hit a syntax error if you try to keep
them.

 

[2007]

»¶Ó­²Î¼ÓÐÂÎÅ×é¡£

 

[RJ]

Sometime I feel like such a rookie! I had LONG forgotten (obviously) that
when you define a Child Field and Master Field that it does perform a requery
on the subform when updating the Master.

Sometime I feel as though I should send you a check for consulting services.
Let's just say you have my utmost regard.

I do have a follow-up question. In removing the child/master links I have
assumed that I must take care of timing of subform requerying in code. And
it works just fine. Is there a way to accomplish this in an ADP without code?

Currently in the main form when a linked record value changes (Current or
AfterUpdate) I am calling a procedure to requery the related subforms with
the new linked value. While this does not add a great deal code I was
wondering if there was something more direct.

Thanks again for all your kind assistance!
RJ

 

[Sylvain Lafontaine]

The subform will be requeried automatically if you set its RecordSource to
the name of a stored procedure and RecordSourceQualifier to dbo if Access
can find a relationship between the name of the parameters of this stored
procedure and the values on the main form.

Personnally, for subforms, I always use SP with a single parameter whose
name is the same as the primary key of the main form; furthermore, I always
define the UniqueTable and ResyncCommand properties of the main form (and of
the subform as well). However, it's my understanding that all this it's not
mandatory. For example, if the name of one of the parameter has the same
name as one of the control on the main form or the name of one of the fields
of its recordset, Access should be able to make a reference to it. I don't
remember if you must add the @ at the beginning of the name and also I don't
remember if Access will be confuse if you use the same name for both the
control and its field (int the case of a bound control). I don't remember if
Child and Master Fields can be used in this context (ie, as a parameter to a
stored procedure).

Your mileage might vary, so you will have to make some tests. If I remember
correctly, there were also many differences between Access 2000 and 2002;
while 2003 was identical to 2002. For 2007, I don't know. Personally, when
the parameter for the subform is not the primary key, I always use the EXEC
method that I reset in the OnCurrent event and I never use the Child and
Master fields for a subform with ADP.

Also, the Server Filter is never used as a parameter to a SP but it can also
trigger a requery (and don't work well or at all with an EXEC statement).
Personally, I never use it either.

 

[Sylvain Lafontaine]

Also, if you are going to make tests, don't forget to make a liberal use of
the Refresh function (or F5 key) for the Views/SP/Functions window each time
you change the parameters of a SP. It might also be a good idea to
compact/repair the project the project to make sure that Access has
forgotten any previous setting when making change to the parameters of a SP.

 

[jayway]

Question
Is it better to use View or SP for recordsource of the Form for data entry?
Thanks

 

[Sylvain Lafontaine]

SPs are more powerful than Views, so you should use them wherever as
possible. In all cases and unlike with MDB file and sometimes (but not
always) with linked ODBC tables, the work is usually always done on the
SQL-Server and not locally; so technically, there is no difference of speed
between a View and a SP. (Which, by the way, doesn't mean that a particular
view will necessarily perform at the same speed that a particular SP. You
must take a look at the execution plan and other things to have a clearer
distinction between a paticular View and a particular SP.).

For filter form, I don't remember if the work is done locally or remotely.

 

[RJ]

Thank you so much for your time and expertise on the subject!

I agree with all your preferred methods regarding form/subform - single PK
etc.

After a few hours of testing (Acc 2003 / SQL 2000) it seems that the
solution that offers the most amount of control is doing through a small
amount of code.

Even with the Child/Master fields blank, as soon as I populate (through
Properties) a Record Source and Qualifier for the subform, that subform gets
re-queried when the main form record is updated. When I say updated I mean
that something in the main record is saved but having nothing to do with the
PK / linkage field. In other words an unnecessary requery. Given that I
have multiple subforms the network traffic overhead is huge!

After a few hours of testing (Acc 2003 / SQL 2000) it seems that the
solution that offers the most amount of control is doing through a small
amount of code.

When there is a change in value in the linkage field, either in the Current
or AfterUpdate event, I set the subform record source via code to
e.g. RecordSource = "Exec dbo.spsf_EventFunctions " & EventId.

By doing this “manually†it only requeries when it really needs to

RJ

 

[Sylvain Lafontaine]

Yeah, using the EXEC statement is the best way to suppress unecessary
requeries, not only for subforms but also for controls (comboboxes and
listboxes) on these subforms (and on the main form? I don't remember).

The unecessary network traffic overhead is probably one of the reason why MS
is in the process of replacing ADO and ADP with .NET.