RecordSource parameters

  • Thread starter Thread starter paul
  • Start date Start date
P

paul

I'm using Access 97. I have a subform, and the
RecordSource of the subform is a query that contains a
parameter. I am prompted to enter the value of the
parameter every time I navigate to a different record in
the parent form. However, I want to set the parameter's
value in code, avoiding manual entry.

I need to set the parameter value, and requery the
subform, every time the parent form navigates to a new
record. Can this be done in code? The value of the
parameter is a field that is available in the parent form.

The link master/link child properties don't solve my
problem because the recordsource of the subform is a
union query, and performance is unacceptable when I rely
on link master/link child properties. The root cause of
this performance probelm is illustrated by the following
two queries. They are equivalent, but the first one
executes MUCH faster:

Select * From Q1 Where Fld = value_1 UNION
Select * From Q2 Where Fld = value_1


Select * From
(Select * From Q1 UNION Select * From Q2)
Where Fld = value_1

The second query is effectively what I get when I use the
link master/link child properties. So, I want to use a
parameter in the query and set it in code.
Thanks,
Paul
..
 
I am not sure but I think you can use Fld (in the first part of the Union
Query, i.e. the first posted Query) as the LinkChildField of the
Master/Child settings for your Subform Control.

You can also use the reference (preferably, full reference) to the Main Form
Control as the Parameter in the Query being used as the RecordSource for the
Subform. In this case, you may need to use the Requery Method to requery
the Subform.

The problem with the second Query is that the inner Query needs to retrieve
*all* Records AND THEN apply the criteria for selection.
 
paul wrote:

[snip the duplicate post]

Patience Paul, sometimes it takes more than 15 minutes for a
post to make a round trip through the internet and all the
news servers.
 
Or, use code to rewrite the SQL of the subform
or subform query.

(david)
 
I agree with (david). Do this in the _OnLoad of the master form. Make
access do the rest of the work. (In this case).

-[Malcolm]
 
I agree with (david). Do it in the _OnLoad of the master. Make Access work
for you!

[Malcolm]
 
Back
Top