I normally set up a Master/Detail relationship using two tables but recently I had need to use a query for the Detail dataset, using the query's DataSource property to connect to the Master table. My problem is that if I first move the cursor to a record other than the first in the Detail query, then put the Master table into edit mode, the Detail query cursor moves back to the first record in the set, presumably due to a refresh. This does not happen when a table is used for the Detail dataset. I am right in expecting the query to work in the same way as a table for the Detail dataset?

Many thanks,
Ian

On 10/16/2013 9:46 AM, Ian Turner wrote:
> I normally set up a Master/Detail relationship using two tables but recently I had need to use a query for the Detail dataset, using the query's DataSource property to connect to the Master table. My problem is that if I first move the cursor to a record other than the first in the Detail query, then put the Master table into edit mode, the Detail query cursor moves back to the first record in the set, presumably due to a refresh. This does not happen when a table is used for the Detail dataset. I am right in expecting the query to work in the same way as a table for the Detail dataset?

Haven't had to use one in a while but detail dataset relies on ranges to
limit the data (to master record selection) so index as i recall was
fairly important so i would not expect the query to work as well (even a
table without an index might show same symptoms).

Hopefully somebody else has a better answer/solution.

Raul

Ian

If you are going to take on using Queries rather than tables I think you might be best to try to use them to their best effect, rather than relying on linking datasources, as this is quite generic behaviour, and subject to the kind of whims you are observing.

If you write SQL for your child query in the form:

SELECT MyFields
FROM MyTable
WHERE MyTable.KeyFieldOfParent = :ParentID

Then in code call

ChildQuery.Prepare;

When your form opens or shows.

On your Master Query add an AfterOpen and AfterScroll event, and in these events do this:

ChildQuery.Close;
ChildQuery.ParamByName('ParentID').asInteger:= MasterQuery.FieldByName('ParentID').asInteger;
ChildQuery.Open;

You can put this code in a dedicated method to avoid duplication.

In this situation the ChildQuery is under your control and will only be refreshed at the specific moments you choose. This should fix the behaviour you are observing. You may also find it is faster.

Note in the above I have assumed that "ParentID" is an Integer data type. In your case it might be something else. You might even need more than 1 param, if the link between the tables is on more than 1 field.

Adam

Ian,

<< I normally set up a Master/Detail relationship using two tables but
recently I had need to use a query for the Detail dataset, using the query's
DataSource property to connect to the Master table. My problem is that if I
first move the cursor to a record other than the first in the Detail query,
then put the Master table into edit mode, the Detail query cursor moves back
to the first record in the set, presumably due to a refresh. This does not
happen when a table is used for the Detail dataset. I am right in expecting
the query to work in the same way as a table for the Detail dataset? >>

The issue with queries is that their result set cursors must be
closed/re-opened (freed/created) during execution, whereas a master-detail
linkage simply sets a different range on the target table cursor.  It's the
closing/re-opening that resets the row pointer to the first row in the
detail query.

Tim Young
Elevate Software
www.elevatesoft.com

Thank you everyone for the helpful replies.The AfterScroll event has been most useful; thanks for that idea Adam.

Ian