Recordset in subform based on field in parent form

message from Lyn on 4 Jun 2004

Hi,
I am working on a genealogy form. The only table (so far) lists everybody
in the family, one record per person. Each record has an autonum ID.

The parent form (frmMainForm) displays the data in each record, which
includes the ID of the father and the mother (who also have records in the
table). One record per form.

I have a Tab Control in the form, and in one of the tabs I have a subform
(sfmSiblings) in which I wish to list all the brothers and sisters of the
person currently being displayed in the main form. The basis of the
selection is all records which have the same father ID or mother ID field
values (excluding of course the person displayed in the main form). I am
trying to use the values in the controls on the main form where the father
ID and mother ID are displayed in the subform query.

I created the subform originally as a form with a query in which I hardcoded
test values for the father ID and mother ID. This works fine. Then I added
this form as a subform in the Tab Control of the main form. I viewed the
SQL for the query and updated it by substituting the values of the controls
on the main form for the hardcoding. That is, instead of using the
following format in the SELECT statement:

WHERE Person.IDFather = 5 OR Person.IDMother = 6 ...

I substituted:

WHERE Person.IDFather = frmMainForm!IDFath OR Person.IDMother =
frmMainForm!IDMoth ...

I obviously have this syntax wrong, because when I run the main form, for
each record I get msgboxes prompting for the values frmMainForm!IDFath and
frmMainForm!IDMoth. If I respond with the correct values, the subform
displays the correct data.

I hope that this makes some sense. Essentially my question is: how can I
make the query in the subform use criteria displayed in controls on the main
form?

Another aspect of this is that all the books and tutorials I have seen
assume that the subform will be used to display data from a different but
related table from the record in the main form. In my case, there is only
one table and I want to display related records (via the parent IDs) from
that same table. Am I not allowed to do this? Or will I need some sort of
additional crossreference table?

Thanks in advance for any assistance.

Allen Browne replied to Lyn on 4 Jun 2004

Hi Lyn

WHERE (Person.IDFather = Nz(Forms!frmMainForm!IDFath,0))
OR (Person.IDMother = Nz(Forms!frmMainForm!IDMoth,0))

The Nz() should cope with the case where the main form is at a new record.
However, you will probably get an initial error on load, because Access
loads the subform before the main form.

On your broader question, you can join a table to itself, by dragging
multiple copies of the table into the Relationships window. There is an
example of this with animal pedigrees at:
http://allenbrowne.com/ser-06.html

Lyn replied to Allen Browne on 5 Jun 2004

Allen,
Thanks for your suggestions. The animal pedigree tip looks very similar to
what I am doing. I will give this a try.

BTW, I live in Sydney.

Reggie replied to Lyn on 4 Jun 2004

[Forms]![frmMainForm]![IDFath] Or [Forms]![frmMainForm]![IDMoth]

Hope it helps!

Lyn replied to Reggie on 5 Jun 2004

Thanks Reggie. From all the responses, it looks as though the main thing I
was missing was the "Forms!" prefix. I have gotten too used to the "Me!"
shortcut which of course does not work here.

Archived message: Recordset in subform based on field in parent form (MS Access Forms)