Set form with subforms record source to parameter stored procedure

[tomocb]

I'm trying to set the record source to a parameter based SP depending on
where the form is opened from.

However, I have a problem that the subforms are expecting their child
parameter values even when the subforms are disabled.

How should I work around this problem? Thanks.

[Sylvain Lafontaine]

When the record source of a form is changed, this form automatically
requeried; which means that all of its subforms must also be requeried in
order to keep them in sync.

I would see four possibilities to solve this problem:

1- remove the record source (set it to blank) of all subforms before
changing the record source of the main form and put it back after.

2- or do the same with the LinkMasterField and ChildMasterField: remove them
before and reput them after.

3- or use a constant sql string for the record sources of the subform using
the keyword EXEC to call the SP; for example:

"EXEC MySP parm1, parm2, 'parm3', ...."

As this is a constant string with no longer any parameters depending on
the record source of the main form, this should block the requerying of the
subform. However, with this method, you will have to reset the query string
each time the user navigate the main form in its OnCurrent event.

4- or make sure that the SPs have all the same list of parameters, even if
they don't need them.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Post by tomocb
I'm trying to set the record source to a parameter based SP depending on
where the form is opened from.
However, I have a problem that the subforms are expecting their child
parameter values even when the subforms are disabled.
How should I work around this problem? Thanks.

[Tomo]

On May 17, 5:19 pm, "Sylvain Lafontaine"

Post by Sylvain Lafontaine
When the record source of a form is changed, this form automatically
requeried; which means that all of its subforms must also be requeried in
order to keep them in sync.
1- remove the record source (set it to blank) of all subforms before
changing the record source of the main form and put it back after.
2- or do the same with the LinkMasterField and ChildMasterField: remove them
before and reput them after.
3- or use a constant sql string for the record sources of the subform using
    "EXEC MySP parm1, parm2, 'parm3', ...."
    As this is a constant string with no longer any parameters depending on
the record source of the main form, this should block the requerying of the
subform.  However, with this method, you will have to reset the query string
each time the user navigate the main form in its OnCurrent event.
4- or make sure that the SPs have all the same list of parameters, even if
they don't need them.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site:http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Post by tomocb
I'm trying to set the record source to a parameter based SP depending on
where the form is opened from.
However, I have a problem that the subforms are expecting their child
parameter values even when the subforms are disabled.
How should I work around this problem?  Thanks.- Hide quoted text -

- Show quoted text -

Thanks for the reply Sylvain. Your post makes perfect sense.
However, I still don't understand why my form is still asking for a
parameter value when I open it and change the record source and the
input parameters also. E.g.

Default form properties:
Record source = spProductsResync
Unique Table = tblProducts
Resync Command = {Call spProductsResync(?)}
Input Parameters = @ProductID Int = 0

On Open form properties:
Record source = spProducts
Unique Table = tblProducts
Resync Command = {Call spProductsResync(?)}
Input Parameters =

As you will see from the example that I'm not supplying any parameters
on the On Open event. I'm also clearing the parameters before
changing the record source.

Also, my subform record sources require parameters that equal the
master record ID. It looks like I've sorted the problem of the
subform parameters but gained a problem with the master form one
instead!

[Sylvain Lafontaine]

Post by Tomo
Thanks for the reply Sylvain. Your post makes perfect sense.
However, I still don't understand why my form is still asking for a
parameter value when I open it and change the record source and the
input parameters also. E.g.
Record source = spProductsResync
Unique Table = tblProducts
Resync Command = {Call spProductsResync(?)}
Record source = spProducts
Unique Table = tblProducts
Resync Command = {Call spProductsResync(?)}
Input Parameters =
As you will see from the example that I'm not supplying any parameters
on the On Open event. I'm also clearing the parameters before
changing the record source.
Also, my subform record sources require parameters that equal the
master record ID. It looks like I've sorted the problem of the
subform parameters but gained a problem with the master form one
instead!

If I remember correctly, the InputParameters property is parsed only once
when the form opens, before the OnOpen event; so trying to change it inside
this event shouldn't have any effect.

The record source for the form is also queried before the OnOpen event but
if you change it, it's queried again.

Also, if you want to change the record source when the form opens, it's
better to do it in the OnLoad event instead of the OnOpen event; because in
the second case, the OnLoad event is directly called right after the change
to the record source is made, before the OnOpen event terminates. Only when
the OnLoad event will return that the OnEvent will finish the rest of its
code. If you're not careful, this could lead to some subtil bug.

[Tomo]

On May 18, 7:36 am, "Sylvain Lafontaine"

Post by Sylvain Lafontaine

Thanks for the reply Sylvain.  Your post makes perfect sense.
However, I still don't understand why my form is still asking for a
parameter value when I open it and change the record source and the
input parameters also.  E.g.
Record source = spProductsResync
Unique Table = tblProducts
Resync Command = {Call spProductsResync(?)}
Record source = spProducts
Unique Table = tblProducts
Resync Command = {Call spProductsResync(?)}
Input Parameters =
As you will see from the example that I'm not supplying any parameters
on the On Open event.  I'm also clearing the parameters before
changing the record source.
Also, my subform record sources require parameters that equal the
master record ID.  It looks like I've sorted the problem of the
subform parameters but gained a problem with the master form one
instead!

If I remember correctly, the InputParameters property is parsed only once
when the form opens, before the OnOpen event; so trying to change it inside
this event shouldn't have any effect.
The record source for the form is also queried before the OnOpen event but
if you change it, it's queried again.
Also, if you want to change the record source when the form opens, it's
better to do it in the OnLoad event instead of the OnOpen event; because in
the second case, the OnLoad event is directly called right after the change
to the record source is made, before the OnOpen event terminates.  Only when
the OnLoad event will return that the OnEvent will finish the rest of its
code.  If you're not careful, this could lead to some subtil bug.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site:http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)- Hide quoted text -
- Show quoted text -

Fixed! I was overcomplicating things by having different input parms
for each version of the form. Have reverted to using an EXEC in the
recordsource and now works perfect.

Many thanks for your advice. It is greatly appreciated!

[Sylvain Lafontaine]

Yeah, there's nothing wrong by using an EXEC statement instead of the
InputParameters property and performance is often better because this
eliminates unnecessary/useless requests at the opening of the form.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"Tomo" <***@gmail.com> wrote in message news:f40b36e9-5600-4dd5-8c53-***@y12g2000vbg.googlegroups.com...
On May 18, 7:36 am, "Sylvain Lafontaine"

Post by Sylvain Lafontaine

Thanks for the reply Sylvain. Your post makes perfect sense.
However, I still don't understand why my form is still asking for a
parameter value when I open it and change the record source and the
input parameters also. E.g.
Record source = spProductsResync
Unique Table = tblProducts
Resync Command = {Call spProductsResync(?)}
Record source = spProducts
Unique Table = tblProducts
Resync Command = {Call spProductsResync(?)}
Input Parameters =
As you will see from the example that I'm not supplying any parameters
on the On Open event. I'm also clearing the parameters before
changing the record source.
Also, my subform record sources require parameters that equal the
master record ID. It looks like I've sorted the problem of the
subform parameters but gained a problem with the master form one
instead!

If I remember correctly, the InputParameters property is parsed only once
when the form opens, before the OnOpen event; so trying to change it inside
this event shouldn't have any effect.
The record source for the form is also queried before the OnOpen event but
if you change it, it's queried again.
Also, if you want to change the record source when the form opens, it's
better to do it in the OnLoad event instead of the OnOpen event; because in
the second case, the OnLoad event is directly called right after the change
to the record source is made, before the OnOpen event terminates. Only
when
the OnLoad event will return that the OnEvent will finish the rest of its
code. If you're not careful, this could lead to some subtil bug.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site:http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)- Hide quoted text -
- Show quoted text -

Fixed! I was overcomplicating things by having different input parms
for each version of the form. Have reverted to using an EXEC in the
recordsource and now works perfect.

Many thanks for your advice. It is greatly appreciated!