Next record in one-to-many relationship?

[Tony Dunn]

I have inherited another developer's database (Access 2003) and with it
a problem I'm struggling with. The problem is this.

I have a parent-child form pair, which is populated by a table (the
parent) and a query (the child). The two sources are linked by an
identical unique number. The table populating the parent uses the number
as primary key. The child table has no primary key so it can used
one-to-many. Users add data through the form for a distinct financial
year end period (FYE).

When the child form loads, it brings in data for the first financial
year end period in the table without a primary key. The user wants to
work in the same form with data for each individual financial year end,
and so needs a command button which calls the next record for the unique
number presently loaded into the form (via a text box) by its next
financial year end, so they can work on data for each financial year.

What I need to do is code something for a command button that takes the
unique number (available through the .Text property of the textbox on
the child form, which is where the data gets added) and the FYE data
(available through a textbox property again), and construct a query that
gets the next record in the table using the number and FYE, and loads it
into the parent and child forms. I will also need a button that moves
them back in the opposite direction.

Help much appreciated, because I'm running out of time! :-)

Thanks.

Tony

[Tom van Stiphout]

On Sat, 03 Mar 2007 19:28:39 GMT, Tony Dunn <to********@ntlworld.com>
wrote:

I don't understand.
"The child table has no primary key so it can used one-to-many.": can
you rephrase this?
Please specify the relevant fields of tblParent and qryChild. Give us
the sql of qryChild.
Typically the subform is linked to its parent using LinkChildFields
and LinkMasterFields properties of the subform control. Is your form
set up that way?
EVERY table needs to have a PK. Add one to the child table.

General observation about Next/Previous: there is no such thing as a
Next record in a table. That's right. Next only exists after a sort
order has been specified, either implicitly (e.g. open a table in
datasheet view and by default you get the rows ordered by PK) or
explicitly (using the ORDER BY clause). You probably need the latter.

The Text property of a textbox is only available while that textbox
has focus. When you click your nav button, the textbox won't have
focus. The Value property typically is much better.

-Tom.

>I have inherited another developer's database (Access 2003) and with it
a problem I'm struggling with. The problem is this.

I have a parent-child form pair, which is populated by a table (the
parent) and a query (the child). The two sources are linked by an
identical unique number. The table populating the parent uses the number
as primary key. The child table has no primary key so it can used
one-to-many. Users add data through the form for a distinct financial
year end period (FYE).

When the child form loads, it brings in data for the first financial
year end period in the table without a primary key. The user wants to
work in the same form with data for each individual financial year end,
and so needs a command button which calls the next record for the unique
number presently loaded into the form (via a text box) by its next
financial year end, so they can work on data for each financial year.

What I need to do is code something for a command button that takes the
unique number (available through the .Text property of the textbox on
the child form, which is where the data gets added) and the FYE data
(available through a textbox property again), and construct a query that
gets the next record in the table using the number and FYE, and loads it
into the parent and child forms. I will also need a button that moves
them back in the opposite direction.

Help much appreciated, because I'm running out of time! :-)

Thanks.

Tony