L
Lyn
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.
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.