Recordset in subform based on field in parent form

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.
--
Cheers,
Lyn.

Lyn, just put the following in the criteria line of the query:

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

Hope it helps!
--
Reggie

www.smittysinet.com
----------
"Lyn" <lhancock@ihug.com.au> wrote in message
news:c9p93s$tbg$1@lust.ihug.co.nz...[color=blue]
> 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[/color]
hardcoded[color=blue]
> test values for the father ID and mother ID. This works fine. Then I[/color]
added[color=blue]
> 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[/color]
controls[color=blue]
> 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[/color]
main[color=blue]
> 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[/color]
of[color=blue]
> additional crossreference table?
>
> Thanks in advance for any assistance.
> --
> Cheers,
> Lyn.
>
>[/color]

Hi Lyn

You could try:
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lyn" <lhancock@ihug.com.au> wrote in message
news:c9p93s$tbg$1@lust.ihug.co.nz...[color=blue]
>
> 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[/color]
hardcoded[color=blue]
> test values for the father ID and mother ID. This works fine. Then I[/color]
added[color=blue]
> 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[/color]
controls[color=blue]
> 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[/color]
main[color=blue]
> 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[/color]
of[color=blue]
> additional crossreference table?
>
> Thanks in advance for any assistance.
> --
> Cheers,
> Lyn.[/color]

Lyn wrote:[color=blue]
> 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 ...[/color]

If you make the record source for your subform something like
this it should work:

SELECT * FROM Person WHERE
((Person.IDFather=[Forms]![frmMainForm]![IDFather]) OR
(Person.IDMother=[Forms]![frmMainForm]![IDMother]))
AND (Not (Person.IDperson=[Forms]![frmMainForm]![IDperson]));

I'm assuming that the autonumber ID is called IDperson. That last
line is needed unless you also want to include the person on the
main form in the list of siblings.

[color=blue]
> 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?[/color]

this shouldn't be any problem.

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.

--
Cheers,
Lyn.

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:40c02d53$0$8111$5a62ac22@freenews.iinet.net.a u...[color=blue]
> Hi Lyn
>
> You could try:
> 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
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Lyn" <lhancock@ihug.com.au> wrote in message
> news:c9p93s$tbg$1@lust.ihug.co.nz...[color=green]
> >
> > I am working on a genealogy form. The only table (so far) lists[/color][/color]
everybody[color=blue][color=green]
> > 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[/color][/color]
the[color=blue][color=green]
> > table). One record per form.
> >
> > I have a Tab Control in the form, and in one of the tabs I have a[/color][/color]
subform[color=blue][color=green]
> > (sfmSiblings) in which I wish to list all the brothers and sisters of[/color][/color]
the[color=blue][color=green]
> > 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[/color][/color]
field[color=blue][color=green]
> > values (excluding of course the person displayed in the main form). I[/color][/color]
am[color=blue][color=green]
> > trying to use the values in the controls on the main form where the[/color][/color]
father[color=blue][color=green]
> > ID and mother ID are displayed in the subform query.
> >
> > I created the subform originally as a form with a query in which I[/color]
> hardcoded[color=green]
> > test values for the father ID and mother ID. This works fine. Then I[/color]
> added[color=green]
> > this form as a subform in the Tab Control of the main form. I viewed[/color][/color]
the[color=blue][color=green]
> > SQL for the query and updated it by substituting the values of the[/color]
> controls[color=green]
> > 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,[/color][/color]
for[color=blue][color=green]
> > each record I get msgboxes prompting for the values frmMainForm!IDFath[/color][/color]
and[color=blue][color=green]
> > 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[/color][/color]
I[color=blue][color=green]
> > make the query in the subform use criteria displayed in controls on the[/color]
> main[color=green]
> > 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[/color][/color]
but[color=blue][color=green]
> > related table from the record in the main form. In my case, there is[/color][/color]
only[color=blue][color=green]
> > one table and I want to display related records (via the parent IDs)[/color][/color]
from[color=blue][color=green]
> > that same table. Am I not allowed to do this? Or will I need some sort[/color]
> of[color=green]
> > additional crossreference table?
> >
> > Thanks in advance for any assistance.
> > --
> > Cheers,
> > Lyn.[/color]
>
>[/color]

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.

--
Cheers,
Lyn.

"Reggie" <nospam_chief@smittysinet.com> wrote in message
news:GP2dnTsjn-GLs13dRVn-tA@comcast.com...[color=blue]
> Lyn, just put the following in the criteria line of the query:
>
> [Forms]![frmMainForm]![IDFath] Or [Forms]![frmMainForm]![IDMoth]
>
> Hope it helps!
> --
> Reggie
>
> www.smittysinet.com
> ----------
> "Lyn" <lhancock@ihug.com.au> wrote in message
> news:c9p93s$tbg$1@lust.ihug.co.nz...[color=green]
> > Hi,
> > I am working on a genealogy form. The only table (so far) lists[/color][/color]
everybody[color=blue][color=green]
> > 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[/color][/color]
the[color=blue][color=green]
> > table). One record per form.
> >
> > I have a Tab Control in the form, and in one of the tabs I have a[/color][/color]
subform[color=blue][color=green]
> > (sfmSiblings) in which I wish to list all the brothers and sisters of[/color][/color]
the[color=blue][color=green]
> > 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[/color][/color]
field[color=blue][color=green]
> > values (excluding of course the person displayed in the main form). I[/color][/color]
am[color=blue][color=green]
> > trying to use the values in the controls on the main form where the[/color][/color]
father[color=blue][color=green]
> > ID and mother ID are displayed in the subform query.
> >
> > I created the subform originally as a form with a query in which I[/color]
> hardcoded[color=green]
> > test values for the father ID and mother ID. This works fine. Then I[/color]
> added[color=green]
> > this form as a subform in the Tab Control of the main form. I viewed[/color][/color]
the[color=blue][color=green]
> > SQL for the query and updated it by substituting the values of the[/color]
> controls[color=green]
> > 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,[/color][/color]
for[color=blue][color=green]
> > each record I get msgboxes prompting for the values frmMainForm!IDFath[/color][/color]
and[color=blue][color=green]
> > 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[/color][/color]
I[color=blue][color=green]
> > make the query in the subform use criteria displayed in controls on the[/color]
> main[color=green]
> > 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[/color][/color]
but[color=blue][color=green]
> > related table from the record in the main form. In my case, there is[/color][/color]
only[color=blue][color=green]
> > one table and I want to display related records (via the parent IDs)[/color][/color]
from[color=blue][color=green]
> > that same table. Am I not allowed to do this? Or will I need some sort[/color]
> of[color=green]
> > additional crossreference table?
> >
> > Thanks in advance for any assistance.
> > --
> > Cheers,
> > Lyn.
> >
> >[/color]
>
>[/color]

Hi Helen. You are right, my autonum ID *is* called "IDPerson"! It seems
that the main problem was that I omitted the "Forms!" prefix in the main
form reference.

Thanks to all for your prompt responses.
--
Cheers,
Lyn.

"Helen Wheels" <helenwheelss@yahoo.com.au> wrote in message
news:40C0337E.9000603@yahoo.com.au...[color=blue]
> Lyn wrote:[color=green]
> > Hi,
> > I am working on a genealogy form. The only table (so far) lists[/color][/color]
everybody[color=blue][color=green]
> > 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[/color][/color]
the[color=blue][color=green]
> > table). One record per form.
> >
> > I have a Tab Control in the form, and in one of the tabs I have a[/color][/color]
subform[color=blue][color=green]
> > (sfmSiblings) in which I wish to list all the brothers and sisters of[/color][/color]
the[color=blue][color=green]
> > 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[/color][/color]
field[color=blue][color=green]
> > values (excluding of course the person displayed in the main form). I[/color][/color]
am[color=blue][color=green]
> > trying to use the values in the controls on the main form where the[/color][/color]
father[color=blue][color=green]
> > ID and mother ID are displayed in the subform query.
> >
> > I created the subform originally as a form with a query in which I[/color][/color]
hardcoded[color=blue][color=green]
> > test values for the father ID and mother ID. This works fine. Then I[/color][/color]
added[color=blue][color=green]
> > this form as a subform in the Tab Control of the main form. I viewed[/color][/color]
the[color=blue][color=green]
> > SQL for the query and updated it by substituting the values of the[/color][/color]
controls[color=blue][color=green]
> > 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 ...[/color]
>
> If you make the record source for your subform something like
> this it should work:
>
> SELECT * FROM Person WHERE
> ((Person.IDFather=[Forms]![frmMainForm]![IDFather]) OR
> (Person.IDMother=[Forms]![frmMainForm]![IDMother]))
> AND (Not (Person.IDperson=[Forms]![frmMainForm]![IDperson]));
>
> I'm assuming that the autonumber ID is called IDperson. That last
> line is needed unless you also want to include the person on the
> main form in the list of siblings.
>
>[color=green]
> > I obviously have this syntax wrong, because when I run the main form,[/color][/color]
for[color=blue][color=green]
> > each record I get msgboxes prompting for the values frmMainForm!IDFath[/color][/color]
and[color=blue][color=green]
> > 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[/color][/color]
I[color=blue][color=green]
> > make the query in the subform use criteria displayed in controls on the[/color][/color]
main[color=blue][color=green]
> > 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[/color][/color]
but[color=blue][color=green]
> > related table from the record in the main form. In my case, there is[/color][/color]
only[color=blue][color=green]
> > one table and I want to display related records (via the parent IDs)[/color][/color]
from[color=blue][color=green]
> > that same table. Am I not allowed to do this? Or will I need some sort[/color][/color]
of[color=blue][color=green]
> > additional crossreference table?[/color]
>
> this shouldn't be any problem.
>[/color]

Thanks again to everyone who responded to my original question. I think
Allen was right, using a control value in the main form as a criterion does
not work because the subform loads before the main form.

The Person record must be available before the main form or subform opens.
So I have tried using multiple copies of the table (Person) in the query
(with alias names) with different types of joins. Firstly, just the one
alias (Person_Sib) joined by the IDFather field in each table (I have left
out IDMother for simplicity). The Person table in the query is intended to
be the record in the main form, and the Person_Sib alias is intended to find
all the records with the same IDFather. However, in this case, Person_Sib
just seems to track Person -- ie, Person_Sib returns only the same (single)
record as Person.

So I have tried a different tack. I inserted a second alias of Person
(Person_Father) between the two original tables in the query. In this case,
Person.IDFather is joined to Person_Father.IDPerson (the autonum record ID)
which is joined to Person_Sib.IDFather. At this point I got confused over
what sort of joins to use. Using INNER joins for both (which I am sure is
wrong), Person_Sib still seems to track the same record as Person.

The Person record is the subject of the main form. I need the Person_Sib
alias to list ALL records with the same IDFather (and eventually "OR same
IDMother"). I will of course eventually filter out the Person_Sib record
that matches the Person in the main form (who cannot be his/her own
sibling). How can I do this in the Access query? Or do I have to resort to
VBA to create the sibling recordset? What am I doing wrong? (I hope that
this makes sense.)

Thanks for any further assistance.
--
Cheers,
Lyn.

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:40c02d53$0$8111$5a62ac22@freenews.iinet.net.a u...[color=blue]
> Hi Lyn
>
> You could try:
> 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
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Lyn" <lhancock@ihug.com.au> wrote in message
> news:c9p93s$tbg$1@lust.ihug.co.nz...[color=green]
> >
> > I am working on a genealogy form. The only table (so far) lists[/color][/color]
everybody[color=blue][color=green]
> > 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[/color][/color]
the[color=blue][color=green]
> > table). One record per form.
> >
> > I have a Tab Control in the form, and in one of the tabs I have a[/color][/color]
subform[color=blue][color=green]
> > (sfmSiblings) in which I wish to list all the brothers and sisters of[/color][/color]
the[color=blue][color=green]
> > 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[/color][/color]
field[color=blue][color=green]
> > values (excluding of course the person displayed in the main form). I[/color][/color]
am[color=blue][color=green]
> > trying to use the values in the controls on the main form where the[/color][/color]
father[color=blue][color=green]
> > ID and mother ID are displayed in the subform query.
> >
> > I created the subform originally as a form with a query in which I[/color]
> hardcoded[color=green]
> > test values for the father ID and mother ID. This works fine. Then I[/color]
> added[color=green]
> > this form as a subform in the Tab Control of the main form. I viewed[/color][/color]
the[color=blue][color=green]
> > SQL for the query and updated it by substituting the values of the[/color]
> controls[color=green]
> > 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,[/color][/color]
for[color=blue][color=green]
> > each record I get msgboxes prompting for the values frmMainForm!IDFath[/color][/color]
and[color=blue][color=green]
> > 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[/color][/color]
I[color=blue][color=green]
> > make the query in the subform use criteria displayed in controls on the[/color]
> main[color=green]
> > 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[/color][/color]
but[color=blue][color=green]
> > related table from the record in the main form. In my case, there is[/color][/color]
only[color=blue][color=green]
> > one table and I want to display related records (via the parent IDs)[/color][/color]
from[color=blue][color=green]
> > that same table. Am I not allowed to do this? Or will I need some sort[/color]
> of[color=green]
> > additional crossreference table?
> >
> > Thanks in advance for any assistance.
> > --
> > Cheers,
> > Lyn.[/color]
>
>[/color]

Could you set the subform so it intially loads no-one:
SELECT * FROM MyTable WHERE (False);

Then use the Current event of the main form to assign its RecordSource.
Something like this:
Dim strWhere As String

If Me.NewRecord Then
strWhere = "(False);"
Else
strWhere = "(Person_Father.IDPerson = " & Me.IDPerson & ") OR
(Person_Mother.IDPerson = " & Me.IDPerson & ");"
Me.[Child1].Form.RecordSource = "SELECT * FROM SomeTable WHERE " &
strWhere
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lyn" <lhancock@ihug.com.au> wrote in message
news:c9v7ap$32p$1@lust.ihug.co.nz...[color=blue]
> Thanks again to everyone who responded to my original question. I think
> Allen was right, using a control value in the main form as a criterion[/color]
does[color=blue]
> not work because the subform loads before the main form.
>
> The Person record must be available before the main form or subform opens.
> So I have tried using multiple copies of the table (Person) in the query
> (with alias names) with different types of joins. Firstly, just the one
> alias (Person_Sib) joined by the IDFather field in each table (I have left
> out IDMother for simplicity). The Person table in the query is intended[/color]
to[color=blue]
> be the record in the main form, and the Person_Sib alias is intended to[/color]
find[color=blue]
> all the records with the same IDFather. However, in this case, Person_Sib
> just seems to track Person -- ie, Person_Sib returns only the same[/color]
(single)[color=blue]
> record as Person.
>
> So I have tried a different tack. I inserted a second alias of Person
> (Person_Father) between the two original tables in the query. In this[/color]
case,[color=blue]
> Person.IDFather is joined to Person_Father.IDPerson (the autonum record[/color]
ID)[color=blue]
> which is joined to Person_Sib.IDFather. At this point I got confused over
> what sort of joins to use. Using INNER joins for both (which I am sure is
> wrong), Person_Sib still seems to track the same record as Person.
>
> The Person record is the subject of the main form. I need the Person_Sib
> alias to list ALL records with the same IDFather (and eventually "OR same
> IDMother"). I will of course eventually filter out the Person_Sib record
> that matches the Person in the main form (who cannot be his/her own
> sibling). How can I do this in the Access query? Or do I have to resort[/color]
to[color=blue]
> VBA to create the sibling recordset? What am I doing wrong? (I hope that
> this makes sense.)
>
> Thanks for any further assistance.
> --
> Cheers,
> Lyn.
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:40c02d53$0$8111$5a62ac22@freenews.iinet.net.a u...[color=green]
> > Hi Lyn
> >
> > You could try:
> > 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[/color][/color]
record.[color=blue][color=green]
> > 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
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Lyn" <lhancock@ihug.com.au> wrote in message
> > news:c9p93s$tbg$1@lust.ihug.co.nz...[color=darkred]
> > >
> > > I am working on a genealogy form. The only table (so far) lists[/color][/color]
> everybody[color=green][color=darkred]
> > > 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[/color][/color]
> the[color=green][color=darkred]
> > > table). One record per form.
> > >
> > > I have a Tab Control in the form, and in one of the tabs I have a[/color][/color]
> subform[color=green][color=darkred]
> > > (sfmSiblings) in which I wish to list all the brothers and sisters of[/color][/color]
> the[color=green][color=darkred]
> > > 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[/color][/color]
> field[color=green][color=darkred]
> > > values (excluding of course the person displayed in the main form). I[/color][/color]
> am[color=green][color=darkred]
> > > trying to use the values in the controls on the main form where the[/color][/color]
> father[color=green][color=darkred]
> > > ID and mother ID are displayed in the subform query.
> > >
> > > I created the subform originally as a form with a query in which I[/color]
> > hardcoded[color=darkred]
> > > test values for the father ID and mother ID. This works fine. Then I[/color]
> > added[color=darkred]
> > > this form as a subform in the Tab Control of the main form. I viewed[/color][/color]
> the[color=green][color=darkred]
> > > SQL for the query and updated it by substituting the values of the[/color]
> > controls[color=darkred]
> > > 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,[/color][/color]
> for[color=green][color=darkred]
> > > each record I get msgboxes prompting for the values frmMainForm!IDFath[/color][/color]
> and[color=green][color=darkred]
> > > 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[/color][/color][/color]
can[color=blue]
> I[color=green][color=darkred]
> > > make the query in the subform use criteria displayed in controls on[/color][/color][/color]
the[color=blue][color=green]
> > main[color=darkred]
> > > 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[/color][/color]
> but[color=green][color=darkred]
> > > related table from the record in the main form. In my case, there is[/color][/color]
> only[color=green][color=darkred]
> > > one table and I want to display related records (via the parent IDs)[/color][/color]
> from[color=green][color=darkred]
> > > that same table. Am I not allowed to do this? Or will I need some[/color][/color][/color]
sort[color=blue][color=green]
> > of[color=darkred]
> > > additional crossreference table?
> > >
> > > Thanks in advance for any assistance.
> > > --
> > > Cheers,
> > > Lyn.[/color][/color][/color]

Allen,
I have tried your suggestion and variations of it, but no luck. After a lot
of trial and error, I have resorted to using VBA code similar to what you
provided in your previous. I have simplified the requirements to the bare
minimum, just to get something that works, then I can build it up again.

I now have a form containing a subform (sfmSibs) in a tab control. I have
removed any query from the subform's Record Source.

In the Current event for the main form, which I already use to populate some
unbound controls on the main form, I have added the following code:

Dim mySQL As String

If Not IsNull(Me![IDFather]) And Me![IDFather] <> 0 Then
mySQL = "SELECT Person.SurnameBirth, Person.FirstName, " & _
"Person.DOB, Person.Sex, " & _
"Person.IDPerson FROM Person " & _
"WHERE Person.IDFather = " & Me![IDFather] & ";"
Me.[sfmSibs].Form.RecordSource = mySQL
End If

When I run this, if Me![IDFather] is null or zero, of course it works (blank
form as expected). However, any record with a non-zero Me![IDFather] gives
this error:

Run-time error '2465':
Microsoft Office Access can't find the field "|"
referred to in your expression.

Not very helpful, as I don't have "|" anywhere in the expression. (Once
when I mistyped a word, this error message displayed the word in error, but
not this time.)

When I click Debug, the following line is highlighted:

Me.[sfmSibs].Form.RecordSource = mySQL

I presume that the problem is somewhere in the mySQL string. I have tried
it with and without the terminating semicolon ";" (VBA often seems to insert
that for you), but no effect.

Any ideas how to troubleshoot this when the field in error is quoted as "|"
?!?!

A related question: instead of doing this in the Current event of the main
form, could I do it in the On Click event of the tab control Page? So that
the subform is not populated unless the relevant tab is clicked. Just
trying to learn as much as I can from this.

Thanks again for your help.

--
Cheers,
Lyn.

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:40c33faf$0$29752$5a62ac22@freenews.iinet.net. au...[color=blue]
> Could you set the subform so it intially loads no-one:
> SELECT * FROM MyTable WHERE (False);
>
> Then use the Current event of the main form to assign its RecordSource.
> Something like this:
> Dim strWhere As String
>
> If Me.NewRecord Then
> strWhere = "(False);"
> Else
> strWhere = "(Person_Father.IDPerson = " & Me.IDPerson & ") OR
> (Person_Mother.IDPerson = " & Me.IDPerson & ");"
> Me.[Child1].Form.RecordSource = "SELECT * FROM SomeTable WHERE " &
> strWhere
> End If
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Lyn" <lhancock@ihug.com.au> wrote in message
> news:c9v7ap$32p$1@lust.ihug.co.nz...[color=green]
> > Thanks again to everyone who responded to my original question. I think
> > Allen was right, using a control value in the main form as a criterion[/color]
> does[color=green]
> > not work because the subform loads before the main form.
> >
> > The Person record must be available before the main form or subform[/color][/color]
opens.[color=blue][color=green]
> > So I have tried using multiple copies of the table (Person) in the query
> > (with alias names) with different types of joins. Firstly, just the one
> > alias (Person_Sib) joined by the IDFather field in each table (I have[/color][/color]
left[color=blue][color=green]
> > out IDMother for simplicity). The Person table in the query is intended[/color]
> to[color=green]
> > be the record in the main form, and the Person_Sib alias is intended to[/color]
> find[color=green]
> > all the records with the same IDFather. However, in this case,[/color][/color]
Person_Sib[color=blue][color=green]
> > just seems to track Person -- ie, Person_Sib returns only the same[/color]
> (single)[color=green]
> > record as Person.
> >
> > So I have tried a different tack. I inserted a second alias of Person
> > (Person_Father) between the two original tables in the query. In this[/color]
> case,[color=green]
> > Person.IDFather is joined to Person_Father.IDPerson (the autonum record[/color]
> ID)[color=green]
> > which is joined to Person_Sib.IDFather. At this point I got confused[/color][/color]
over[color=blue][color=green]
> > what sort of joins to use. Using INNER joins for both (which I am sure[/color][/color]
is[color=blue][color=green]
> > wrong), Person_Sib still seems to track the same record as Person.
> >
> > The Person record is the subject of the main form. I need the[/color][/color]
Person_Sib[color=blue][color=green]
> > alias to list ALL records with the same IDFather (and eventually "OR[/color][/color]
same[color=blue][color=green]
> > IDMother"). I will of course eventually filter out the Person_Sib[/color][/color]
record[color=blue][color=green]
> > that matches the Person in the main form (who cannot be his/her own
> > sibling). How can I do this in the Access query? Or do I have to[/color][/color]
resort[color=blue]
> to[color=green]
> > VBA to create the sibling recordset? What am I doing wrong? (I hope[/color][/color]
that[color=blue][color=green]
> > this makes sense.)
> >
> > Thanks for any further assistance.
> > --
> > Cheers,
> > Lyn.
> >
> > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> > news:40c02d53$0$8111$5a62ac22@freenews.iinet.net.a u...[color=darkred]
> > > Hi Lyn
> > >
> > > You could try:
> > > 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[/color][/color]
> record.[color=green][color=darkred]
> > > However, you will probably get an initial error on load, because[/color][/color][/color]
Access[color=blue][color=green][color=darkred]
> > > 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[/color][/color][/color]
an[color=blue][color=green][color=darkred]
> > > example of this with animal pedigrees at:
> > > http://allenbrowne.com/ser-06.html
> > >
> > > --
> > > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > > Tips for Access users - http://allenbrowne.com/tips.html
> > > Reply to group, rather than allenbrowne at mvps dot org.
> > >
> > > "Lyn" <lhancock@ihug.com.au> wrote in message
> > > news:c9p93s$tbg$1@lust.ihug.co.nz...
> > > >
> > > > I am working on a genealogy form. The only table (so far) lists[/color]
> > everybody[color=darkred]
> > > > in the family, one record per person. Each record has an autonum[/color][/color][/color]
ID.[color=blue][color=green][color=darkred]
> > > >
> > > > The parent form (frmMainForm) displays the data in each record,[/color][/color][/color]
which[color=blue][color=green][color=darkred]
> > > > includes the ID of the father and the mother (who also have records[/color][/color][/color]
in[color=blue][color=green]
> > the[color=darkred]
> > > > table). One record per form.
> > > >
> > > > I have a Tab Control in the form, and in one of the tabs I have a[/color]
> > subform[color=darkred]
> > > > (sfmSiblings) in which I wish to list all the brothers and sisters[/color][/color][/color]
of[color=blue][color=green]
> > the[color=darkred]
> > > > 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[/color]
> > field[color=darkred]
> > > > values (excluding of course the person displayed in the main form).[/color][/color][/color]
I[color=blue][color=green]
> > am[color=darkred]
> > > > trying to use the values in the controls on the main form where the[/color]
> > father[color=darkred]
> > > > 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[/color][/color][/color]
I[color=blue][color=green][color=darkred]
> > > added
> > > > this form as a subform in the Tab Control of the main form. I[/color][/color][/color]
viewed[color=blue][color=green]
> > the[color=darkred]
> > > > 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[/color][/color][/color]
form,[color=blue][color=green]
> > for[color=darkred]
> > > > each record I get msgboxes prompting for the values[/color][/color][/color]
frmMainForm!IDFath[color=blue][color=green]
> > and[color=darkred]
> > > > frmMainForm!IDMoth. If I respond with the correct values, the[/color][/color][/color]
subform[color=blue][color=green][color=darkred]
> > > > displays the correct data.
> > > >
> > > > I hope that this makes some sense. Essentially my question is: how[/color][/color]
> can[color=green]
> > I[color=darkred]
> > > > make the query in the subform use criteria displayed in controls on[/color][/color]
> the[color=green][color=darkred]
> > > main
> > > > form?
> > > >
> > > > Another aspect of this is that all the books and tutorials I have[/color][/color][/color]
seen[color=blue][color=green][color=darkred]
> > > > assume that the subform will be used to display data from a[/color][/color][/color]
different[color=blue][color=green]
> > but[color=darkred]
> > > > related table from the record in the main form. In my case, there[/color][/color][/color]
is[color=blue][color=green]
> > only[color=darkred]
> > > > one table and I want to display related records (via the parent IDs)[/color]
> > from[color=darkred]
> > > > that same table. Am I not allowed to do this? Or will I need some[/color][/color]
> sort[color=green][color=darkred]
> > > of
> > > > additional crossreference table?
> > > >
> > > > Thanks in advance for any assistance.
> > > > --
> > > > Cheers,
> > > > Lyn.[/color][/color]
>
>[/color]

Hi Lyn.

To debug this, ask Access to print mySQL out to the immediate window:
mySQL = "SELECT ...
Debug.Print mySQL
Me.[sfmSibs].Form...

Now try it. Open the Immediate window (Ctrl+G). Access will have printed the
faulty SQL statement there. Copy it to clipboard. Then create a new query,
switch it to SQL View (View menu), and paste in the faulty SQL string. This
should help you to see what's wrong, and you will see how to fix the
assigment to mySQL.

The pipe character is a place holder for the field name. It appears that you
are getting the error message without the correct name being inserted into
the place holder. Ultimately it means that Access can't make sense of the
SQL statement. For example, if IDFather is actually a Text field (not a
Number field), then the SQL statement needs exta quote marks:
"WHERE Person.IDFather = """ & Me![IDFather] & """;"

Another cause of the error is Name AutoCorrect incorrectly tracking the
field name. Uncheck the boxes under Tools | Options | General | Name
AutoCorrect, and then compact the database (Tools | Database Utilities).

You're on the right track, so it is worth continuing to debug this. Do
include the trailing semicolon in the sql statement.

It would be possible to use the Change event of the tab control (not the
Click event of the page) rather than the Current event of the main form. The
problem with that approach is that it leaves the wrong data showing in the
subform when you change record in the main form, until you move to a
different tab and move back to the one you want again.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lyn" <lhancock@ihug.com.au> wrote in message
news:ca68e0$j3b$1@lust.ihug.co.nz...[color=blue]
> Allen,
> I have tried your suggestion and variations of it, but no luck. After a[/color]
lot[color=blue]
> of trial and error, I have resorted to using VBA code similar to what you
> provided in your previous. I have simplified the requirements to the bare
> minimum, just to get something that works, then I can build it up again.
>
> I now have a form containing a subform (sfmSibs) in a tab control. I have
> removed any query from the subform's Record Source.
>
> In the Current event for the main form, which I already use to populate[/color]
some[color=blue]
> unbound controls on the main form, I have added the following code:
>
> Dim mySQL As String
>
> If Not IsNull(Me![IDFather]) And Me![IDFather] <> 0 Then
> mySQL = "SELECT Person.SurnameBirth, Person.FirstName, " & _
> "Person.DOB, Person.Sex, " & _
> "Person.IDPerson FROM Person " & _
> "WHERE Person.IDFather = " & Me![IDFather] & ";"
> Me.[sfmSibs].Form.RecordSource = mySQL
> End If
>
> When I run this, if Me![IDFather] is null or zero, of course it works[/color]
(blank[color=blue]
> form as expected). However, any record with a non-zero Me![IDFather][/color]
gives[color=blue]
> this error:
>
> Run-time error '2465':
> Microsoft Office Access can't find the field "|"
> referred to in your expression.
>
> Not very helpful, as I don't have "|" anywhere in the expression. (Once
> when I mistyped a word, this error message displayed the word in error,[/color]
but[color=blue]
> not this time.)
>
> When I click Debug, the following line is highlighted:
>
> Me.[sfmSibs].Form.RecordSource = mySQL
>
> I presume that the problem is somewhere in the mySQL string. I have tried
> it with and without the terminating semicolon ";" (VBA often seems to[/color]
insert[color=blue]
> that for you), but no effect.
>
> Any ideas how to troubleshoot this when the field in error is quoted as[/color]
"|"[color=blue]
> ?!?!
>
> A related question: instead of doing this in the Current event of the main
> form, could I do it in the On Click event of the tab control Page? So[/color]
that[color=blue]
> the subform is not populated unless the relevant tab is clicked. Just
> trying to learn as much as I can from this.
>
> Thanks again for your help.
>
> --
> Cheers,
> Lyn.
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:40c33faf$0$29752$5a62ac22@freenews.iinet.net. au...[color=green]
> > Could you set the subform so it intially loads no-one:
> > SELECT * FROM MyTable WHERE (False);
> >
> > Then use the Current event of the main form to assign its RecordSource.
> > Something like this:
> > Dim strWhere As String
> >
> > If Me.NewRecord Then
> > strWhere = "(False);"
> > Else
> > strWhere = "(Person_Father.IDPerson = " & Me.IDPerson & ") OR
> > (Person_Mother.IDPerson = " & Me.IDPerson & ");"
> > Me.[Child1].Form.RecordSource = "SELECT * FROM SomeTable WHERE "[/color][/color]
&[color=blue][color=green]
> > strWhere
> > End If
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Lyn" <lhancock@ihug.com.au> wrote in message
> > news:c9v7ap$32p$1@lust.ihug.co.nz...[color=darkred]
> > > Thanks again to everyone who responded to my original question. I[/color][/color][/color]
think[color=blue][color=green][color=darkred]
> > > Allen was right, using a control value in the main form as a criterion[/color]
> > does[color=darkred]
> > > not work because the subform loads before the main form.
> > >
> > > The Person record must be available before the main form or subform[/color][/color]
> opens.[color=green][color=darkred]
> > > So I have tried using multiple copies of the table (Person) in the[/color][/color][/color]
query[color=blue][color=green][color=darkred]
> > > (with alias names) with different types of joins. Firstly, just the[/color][/color][/color]
one[color=blue][color=green][color=darkred]
> > > alias (Person_Sib) joined by the IDFather field in each table (I have[/color][/color]
> left[color=green][color=darkred]
> > > out IDMother for simplicity). The Person table in the query is[/color][/color][/color]
intended[color=blue][color=green]
> > to[color=darkred]
> > > be the record in the main form, and the Person_Sib alias is intended[/color][/color][/color]
to[color=blue][color=green]
> > find[color=darkred]
> > > all the records with the same IDFather. However, in this case,[/color][/color]
> Person_Sib[color=green][color=darkred]
> > > just seems to track Person -- ie, Person_Sib returns only the same[/color]
> > (single)[color=darkred]
> > > record as Person.
> > >
> > > So I have tried a different tack. I inserted a second alias of Person
> > > (Person_Father) between the two original tables in the query. In this[/color]
> > case,[color=darkred]
> > > Person.IDFather is joined to Person_Father.IDPerson (the autonum[/color][/color][/color]
record[color=blue][color=green]
> > ID)[color=darkred]
> > > which is joined to Person_Sib.IDFather. At this point I got confused[/color][/color]
> over[color=green][color=darkred]
> > > what sort of joins to use. Using INNER joins for both (which I am[/color][/color][/color]
sure[color=blue]
> is[color=green][color=darkred]
> > > wrong), Person_Sib still seems to track the same record as Person.
> > >
> > > The Person record is the subject of the main form. I need the[/color][/color]
> Person_Sib[color=green][color=darkred]
> > > alias to list ALL records with the same IDFather (and eventually "OR[/color][/color]
> same[color=green][color=darkred]
> > > IDMother"). I will of course eventually filter out the Person_Sib[/color][/color]
> record[color=green][color=darkred]
> > > that matches the Person in the main form (who cannot be his/her own
> > > sibling). How can I do this in the Access query? Or do I have to[/color][/color]
> resort[color=green]
> > to[color=darkred]
> > > VBA to create the sibling recordset? What am I doing wrong? (I hope[/color][/color]
> that[color=green][color=darkred]
> > > this makes sense.)
> > >
> > > Thanks for any further assistance.
> > > --
> > > Cheers,
> > > Lyn.
> > >
> > > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> > > news:40c02d53$0$8111$5a62ac22@freenews.iinet.net.a u...
> > > > Hi Lyn
> > > >
> > > > You could try:
> > > > 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[/color]
> > record.[color=darkred]
> > > > However, you will probably get an initial error on load, because[/color][/color]
> Access[color=green][color=darkred]
> > > > loads the subform before the main form.
> > > >
> > > > On your broader question, you can join a table to itself, by[/color][/color][/color]
dragging[color=blue][color=green][color=darkred]
> > > > multiple copies of the table into the Relationships window. There is[/color][/color]
> an[color=green][color=darkred]
> > > > example of this with animal pedigrees at:
> > > > http://allenbrowne.com/ser-06.html
> > > >
> > > > "Lyn" <lhancock@ihug.com.au> wrote in message
> > > > news:c9p93s$tbg$1@lust.ihug.co.nz...
> > > > >
> > > > > 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[/color][/color]
> ID.[color=green][color=darkred]
> > > > >
> > > > > The parent form (frmMainForm) displays the data in each record,[/color][/color]
> which[color=green][color=darkred]
> > > > > includes the ID of the father and the mother (who also have[/color][/color][/color]
records[color=blue]
> in[color=green][color=darkred]
> > > 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[/color][/color]
> of[color=green][color=darkred]
> > > the
> > > > > person currently being displayed in the main form. The basis of[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > > selection is all records which have the same father ID or mother[/color][/color][/color]
ID[color=blue][color=green][color=darkred]
> > > field
> > > > > values (excluding of course the person displayed in the main[/color][/color][/color]
form).[color=blue]
> I[color=green][color=darkred]
> > > am
> > > > > trying to use the values in the controls on the main form where[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > 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.[/color][/color][/color]
Then[color=blue]
> I[color=green][color=darkred]
> > > > added
> > > > > this form as a subform in the Tab Control of the main form. I[/color][/color]
> viewed[color=green][color=darkred]
> > > 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[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > > following format in the SELECT statement:
> > > > >
> > > > > WHERE Person.IDFather = 5 OR Person.IDMother = 6 ...
> > > > >
> > > > > I substituted:
> > > > >
> > > > > WHERE Person.IDFather = frmMainForm!IDFath OR Person.IDMother[/color][/color][/color]
=[color=blue][color=green][color=darkred]
> > > > > frmMainForm!IDMoth ...
> > > > >
> > > > > I obviously have this syntax wrong, because when I run the main[/color][/color]
> form,[color=green][color=darkred]
> > > for
> > > > > each record I get msgboxes prompting for the values[/color][/color]
> frmMainForm!IDFath[color=green][color=darkred]
> > > and
> > > > > frmMainForm!IDMoth. If I respond with the correct values, the[/color][/color]
> subform[color=green][color=darkred]
> > > > > displays the correct data.
> > > > >
> > > > > I hope that this makes some sense. Essentially my question is:[/color][/color][/color]
how[color=blue][color=green]
> > can[color=darkred]
> > > I
> > > > > make the query in the subform use criteria displayed in controls[/color][/color][/color]
on[color=blue][color=green]
> > the[color=darkred]
> > > > main
> > > > > form?
> > > > >
> > > > > Another aspect of this is that all the books and tutorials I have[/color][/color]
> seen[color=green][color=darkred]
> > > > > assume that the subform will be used to display data from a[/color][/color]
> different[color=green][color=darkred]
> > > but
> > > > > related table from the record in the main form. In my case, there[/color][/color]
> is[color=green][color=darkred]
> > > only
> > > > > one table and I want to display related records (via the parent[/color][/color][/color]
IDs)[color=blue][color=green][color=darkred]
> > > from
> > > > > that same table. Am I not allowed to do this? Or will I need[/color][/color][/color]
some[color=blue][color=green]
> > sort[color=darkred]
> > > > of
> > > > > additional crossreference table?
> > > > >
> > > > > Thanks in advance for any assistance.
> > > > > --
> > > > > Cheers,
> > > > > Lyn.[/color][/color][/color]

Thanks again, Allen. This has given me some good debugging tools.

I did as you suggested, all steps, but without any improvement.

However, I did discover the main problem. Rather than continually update
the same subform and same query, I had created new versions of each. In the
end I had about eight versions of the subform and a similar number of
queries. Updating the query version of a form or subform is easy -- you
just update the Record Source property. But I could not find any analogue
of this for updating the subform version in the Form properties. I
suspected that I might have had the wrong subform version configured in the
main form. The only way I could find to fix this was to delete the current
subform and to recreate it from the Toolbox so that I could get into the
Subform Wizard. Is there any other way to invoke the Subform Wizard?

Anyway, after deleting and recreating the subform in the tab control page of
the main form, the error went away.

I now get data in the subform, but it is always only the same (single)
Person record as in the main form. That is, if I have a number of brothers
and sisters, all with the same IDFather, only my own record is displayed in
the subform. Which I eventually want to suppress, leaving only brothers and
sisters. It seems that instead of the subforms Record Source being the
whole of the Person table, it is being restricted to the one record already
selected by the main form. I had expected the VBA effectively to create a
new recordset (I have successfully used such recordsets in this manner
elsewhere in the main form VBA). How can I make the VBA for the subform
create a new Record Source based on the WHOLE of the Person table, rather
than the single record already selected by the main form?

I feel that I am getting closer to the solution, there is just one piece of
the puzzle still missing.

--
Cheers,
Lyn.

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:40c6be3b$0$29803$5a62ac22@freenews.iinet.net. au...[color=blue]
> Hi Lyn.
>
> To debug this, ask Access to print mySQL out to the immediate window:
> mySQL = "SELECT ...
> Debug.Print mySQL
> Me.[sfmSibs].Form...
>
> Now try it. Open the Immediate window (Ctrl+G). Access will have printed[/color]
the[color=blue]
> faulty SQL statement there. Copy it to clipboard. Then create a new query,
> switch it to SQL View (View menu), and paste in the faulty SQL string.[/color]
This[color=blue]
> should help you to see what's wrong, and you will see how to fix the
> assigment to mySQL.
>
> The pipe character is a place holder for the field name. It appears that[/color]
you[color=blue]
> are getting the error message without the correct name being inserted into
> the place holder. Ultimately it means that Access can't make sense of the
> SQL statement. For example, if IDFather is actually a Text field (not a
> Number field), then the SQL statement needs exta quote marks:
> "WHERE Person.IDFather = """ & Me![IDFather] & """;"
>
> Another cause of the error is Name AutoCorrect incorrectly tracking the
> field name. Uncheck the boxes under Tools | Options | General | Name
> AutoCorrect, and then compact the database (Tools | Database Utilities).
>
> You're on the right track, so it is worth continuing to debug this. Do
> include the trailing semicolon in the sql statement.
>
> It would be possible to use the Change event of the tab control (not the
> Click event of the page) rather than the Current event of the main form.[/color]
The[color=blue]
> problem with that approach is that it leaves the wrong data showing in the
> subform when you change record in the main form, until you move to a
> different tab and move back to the one you want again.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Lyn" <lhancock@ihug.com.au> wrote in message
> news:ca68e0$j3b$1@lust.ihug.co.nz...[color=green]
> > Allen,
> > I have tried your suggestion and variations of it, but no luck. After a[/color]
> lot[color=green]
> > of trial and error, I have resorted to using VBA code similar to what[/color][/color]
you[color=blue][color=green]
> > provided in your previous. I have simplified the requirements to the[/color][/color]
bare[color=blue][color=green]
> > minimum, just to get something that works, then I can build it up again.
> >
> > I now have a form containing a subform (sfmSibs) in a tab control. I[/color][/color]
have[color=blue][color=green]
> > removed any query from the subform's Record Source.
> >
> > In the Current event for the main form, which I already use to populate[/color]
> some[color=green]
> > unbound controls on the main form, I have added the following code:
> >
> > Dim mySQL As String
> >
> > If Not IsNull(Me![IDFather]) And Me![IDFather] <> 0 Then
> > mySQL = "SELECT Person.SurnameBirth, Person.FirstName, " & _
> > "Person.DOB, Person.Sex, " & _
> > "Person.IDPerson FROM Person " & _
> > "WHERE Person.IDFather = " & Me![IDFather] & ";"
> > Me.[sfmSibs].Form.RecordSource = mySQL
> > End If
> >
> > When I run this, if Me![IDFather] is null or zero, of course it works[/color]
> (blank[color=green]
> > form as expected). However, any record with a non-zero Me![IDFather][/color]
> gives[color=green]
> > this error:
> >
> > Run-time error '2465':
> > Microsoft Office Access can't find the field "|"
> > referred to in your expression.
> >
> > Not very helpful, as I don't have "|" anywhere in the expression. (Once
> > when I mistyped a word, this error message displayed the word in error,[/color]
> but[color=green]
> > not this time.)
> >
> > When I click Debug, the following line is highlighted:
> >
> > Me.[sfmSibs].Form.RecordSource = mySQL
> >
> > I presume that the problem is somewhere in the mySQL string. I have[/color][/color]
tried[color=blue][color=green]
> > it with and without the terminating semicolon ";" (VBA often seems to[/color]
> insert[color=green]
> > that for you), but no effect.
> >
> > Any ideas how to troubleshoot this when the field in error is quoted as[/color]
> "|"[color=green]
> > ?!?!
> >
> > A related question: instead of doing this in the Current event of the[/color][/color]
main[color=blue][color=green]
> > form, could I do it in the On Click event of the tab control Page? So[/color]
> that[color=green]
> > the subform is not populated unless the relevant tab is clicked. Just
> > trying to learn as much as I can from this.
> >
> > Thanks again for your help.
> >
> > --
> > Cheers,
> > Lyn.
> >[/color][/color]

Good: you are making progress with debugging.

To change which form gets loaded into your subform control without running
the wizard, you could changethe SourceObject of the subform control.

To help you get the desired SQL statement, create a new query and enter some
dummy values for the person fields. If you have multiple copies of the
Person table, use an alias (properties box) to help assign a meaningful
name. When the query is working the way you need, switch it to SQL View
(View menu), and you have a sample of the SQL string you need to create.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lyn" <lhancock@ihug.com.au> wrote in message
news:ca90fq$f0e$1@lust.ihug.co.nz...[color=blue]
> Thanks again, Allen. This has given me some good debugging tools.
>
> I did as you suggested, all steps, but without any improvement.
>
> However, I did discover the main problem. Rather than continually update
> the same subform and same query, I had created new versions of each. In[/color]
the[color=blue]
> end I had about eight versions of the subform and a similar number of
> queries. Updating the query version of a form or subform is easy -- you
> just update the Record Source property. But I could not find any analogue
> of this for updating the subform version in the Form properties. I
> suspected that I might have had the wrong subform version configured in[/color]
the[color=blue]
> main form. The only way I could find to fix this was to delete the[/color]
current[color=blue]
> subform and to recreate it from the Toolbox so that I could get into the
> Subform Wizard. Is there any other way to invoke the Subform Wizard?
>
> Anyway, after deleting and recreating the subform in the tab control page[/color]
of[color=blue]
> the main form, the error went away.
>
> I now get data in the subform, but it is always only the same (single)
> Person record as in the main form. That is, if I have a number of[/color]
brothers[color=blue]
> and sisters, all with the same IDFather, only my own record is displayed[/color]
in[color=blue]
> the subform. Which I eventually want to suppress, leaving only brothers[/color]
and[color=blue]
> sisters. It seems that instead of the subforms Record Source being the
> whole of the Person table, it is being restricted to the one record[/color]
already[color=blue]
> selected by the main form. I had expected the VBA effectively to create a
> new recordset (I have successfully used such recordsets in this manner
> elsewhere in the main form VBA). How can I make the VBA for the subform
> create a new Record Source based on the WHOLE of the Person table, rather
> than the single record already selected by the main form?
>
> I feel that I am getting closer to the solution, there is just one piece[/color]
of[color=blue]
> the puzzle still missing.
>
> --
> Cheers,
> Lyn.
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:40c6be3b$0$29803$5a62ac22@freenews.iinet.net. au...[color=green]
> > Hi Lyn.
> >
> > To debug this, ask Access to print mySQL out to the immediate window:
> > mySQL = "SELECT ...
> > Debug.Print mySQL
> > Me.[sfmSibs].Form...
> >
> > Now try it. Open the Immediate window (Ctrl+G). Access will have printed[/color]
> the[color=green]
> > faulty SQL statement there. Copy it to clipboard. Then create a new[/color][/color]
query,[color=blue][color=green]
> > switch it to SQL View (View menu), and paste in the faulty SQL string.[/color]
> This[color=green]
> > should help you to see what's wrong, and you will see how to fix the
> > assigment to mySQL.
> >
> > The pipe character is a place holder for the field name. It appears that[/color]
> you[color=green]
> > are getting the error message without the correct name being inserted[/color][/color]
into[color=blue][color=green]
> > the place holder. Ultimately it means that Access can't make sense of[/color][/color]
the[color=blue][color=green]
> > SQL statement. For example, if IDFather is actually a Text field (not a
> > Number field), then the SQL statement needs exta quote marks:
> > "WHERE Person.IDFather = """ & Me![IDFather] & """;"
> >
> > Another cause of the error is Name AutoCorrect incorrectly tracking the
> > field name. Uncheck the boxes under Tools | Options | General | Name
> > AutoCorrect, and then compact the database (Tools | Database Utilities).
> >
> > You're on the right track, so it is worth continuing to debug this. Do
> > include the trailing semicolon in the sql statement.
> >
> > It would be possible to use the Change event of the tab control (not the
> > Click event of the page) rather than the Current event of the main form.[/color]
> The[color=green]
> > problem with that approach is that it leaves the wrong data showing in[/color][/color]
the[color=blue][color=green]
> > subform when you change record in the main form, until you move to a
> > different tab and move back to the one you want again.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Lyn" <lhancock@ihug.com.au> wrote in message
> > news:ca68e0$j3b$1@lust.ihug.co.nz...[color=darkred]
> > > Allen,
> > > I have tried your suggestion and variations of it, but no luck. After[/color][/color][/color]
a[color=blue][color=green]
> > lot[color=darkred]
> > > of trial and error, I have resorted to using VBA code similar to what[/color][/color]
> you[color=green][color=darkred]
> > > provided in your previous. I have simplified the requirements to the[/color][/color]
> bare[color=green][color=darkred]
> > > minimum, just to get something that works, then I can build it up[/color][/color][/color]
again.[color=blue][color=green][color=darkred]
> > >
> > > I now have a form containing a subform (sfmSibs) in a tab control. I[/color][/color]
> have[color=green][color=darkred]
> > > removed any query from the subform's Record Source.
> > >
> > > In the Current event for the main form, which I already use to[/color][/color][/color]
populate[color=blue][color=green]
> > some[color=darkred]
> > > unbound controls on the main form, I have added the following code:
> > >
> > > Dim mySQL As String
> > >
> > > If Not IsNull(Me![IDFather]) And Me![IDFather] <> 0 Then
> > > mySQL = "SELECT Person.SurnameBirth, Person.FirstName, " &[/color][/color][/color]
_[color=blue][color=green][color=darkred]
> > > "Person.DOB, Person.Sex, " & _
> > > "Person.IDPerson FROM Person " & _
> > > "WHERE Person.IDFather = " & Me![IDFather] & ";"
> > > Me.[sfmSibs].Form.RecordSource = mySQL
> > > End If
> > >
> > > When I run this, if Me![IDFather] is null or zero, of course it works[/color]
> > (blank[color=darkred]
> > > form as expected). However, any record with a non-zero Me![IDFather][/color]
> > gives[color=darkred]
> > > this error:
> > >
> > > Run-time error '2465':
> > > Microsoft Office Access can't find the field "|"
> > > referred to in your expression.
> > >
> > > Not very helpful, as I don't have "|" anywhere in the expression.[/color][/color][/color]
(Once[color=blue][color=green][color=darkred]
> > > when I mistyped a word, this error message displayed the word in[/color][/color][/color]
error,[color=blue][color=green]
> > but[color=darkred]
> > > not this time.)
> > >
> > > When I click Debug, the following line is highlighted:
> > >
> > > Me.[sfmSibs].Form.RecordSource = mySQL
> > >
> > > I presume that the problem is somewhere in the mySQL string. I have[/color][/color]
> tried[color=green][color=darkred]
> > > it with and without the terminating semicolon ";" (VBA often seems to[/color]
> > insert[color=darkred]
> > > that for you), but no effect.
> > >
> > > Any ideas how to troubleshoot this when the field in error is quoted[/color][/color][/color]
as[color=blue][color=green]
> > "|"[color=darkred]
> > > ?!?!
> > >
> > > A related question: instead of doing this in the Current event of the[/color][/color]
> main[color=green][color=darkred]
> > > form, could I do it in the On Click event of the tab control Page? So[/color]
> > that[color=darkred]
> > > the subform is not populated unless the relevant tab is clicked. Just
> > > trying to learn as much as I can from this.
> > >
> > > Thanks again for your help.
> > >
> > > --
> > > Cheers,
> > > Lyn.[/color][/color][/color]

Allen,
Thanks again for the help and encouragement. And patience!

I did not fully understand your previous suggestions. I have had queries
and subforms with the IDFather value hardcoded working from the beginning.
The problem has been in getting the query and subform integrated into the
main form, using a value in the main form in place of the hardcoded value,
and using the full table to select from instead of just the current record
in the main form. I tried creating an alias of the table in the SQL by
using the syntax:

SELECT * FROM table AS alias WHERE alias.xxx = ...

But this gave me the same result as not using the alias. That is, it just
seems to give the record already selected in the main form a new table name.
What is needed is the alias to reflect a new copy of the whole table. Maybe
I am not applying the alias properly, although this was all I could find in
the Help file.

I could not find a SourceObject property in the subform control. Only
RecordSource, which points to the dummy query used when first opening the
form.

However, I do have this bit of code working, using the Immediate Pane as a
pseudo subform:

Dim Conn As ADODB.Connection
Set Conn = CurrentProject.Connection
Dim RS As New ADODB.Recordset

If Not IsNull(Me![IDFather]) And Me![IDFather] <> 0 Then
mySQL = "SELECT Person.Surname, Person.FirstName, Person.DOB,
Person.Sex, " & _
"Person.IDPerson FROM Person " & _
"WHERE Person.IDFather = " & Me![IDFather] & _
" AND Person.IDPerson <> " & Me![IDPerson] & ";"
RS.Open mySQL, Conn, adOpenStatic
If RS.RecordCount > 0 Then
RS.MoveFirst
While Not RS.EOF
Debug.Print "Name: " & RS!Surname & " " & RS!FirstName
RS.MoveNext
Wend
End If
RS.Close
End If

This displays all the person's siblings in the debug pane -- ie, all records
in the Person table with the same IDFather value, excluding the record
currently displayed in the main form.

All I have to do now is find out how to display the same results in the
subform instead of the debug window. A simple task! (You would think?) So
what am I still doing wrong?

--
Cheers,
Lyn.

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:40c82049$0$29752$5a62ac22@freenews.iinet.net. au...[color=blue]
> Good: you are making progress with debugging.
>
> To change which form gets loaded into your subform control without running
> the wizard, you could changethe SourceObject of the subform control.
>
> To help you get the desired SQL statement, create a new query and enter[/color]
some[color=blue]
> dummy values for the person fields. If you have multiple copies of the
> Person table, use an alias (properties box) to help assign a meaningful
> name. When the query is working the way you need, switch it to SQL View
> (View menu), and you have a sample of the SQL string you need to create.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Lyn" <lhancock@ihug.com.au> wrote in message
> news:ca90fq$f0e$1@lust.ihug.co.nz...[color=green]
> > Thanks again, Allen. This has given me some good debugging tools.
> >
> > I did as you suggested, all steps, but without any improvement.
> >
> > However, I did discover the main problem. Rather than continually[/color][/color]
update[color=blue][color=green]
> > the same subform and same query, I had created new versions of each. In[/color]
> the[color=green]
> > end I had about eight versions of the subform and a similar number of
> > queries. Updating the query version of a form or subform is easy -- you
> > just update the Record Source property. But I could not find any[/color][/color]
analogue[color=blue][color=green]
> > of this for updating the subform version in the Form properties. I
> > suspected that I might have had the wrong subform version configured in[/color]
> the[color=green]
> > main form. The only way I could find to fix this was to delete the[/color]
> current[color=green]
> > subform and to recreate it from the Toolbox so that I could get into the
> > Subform Wizard. Is there any other way to invoke the Subform Wizard?
> >
> > Anyway, after deleting and recreating the subform in the tab control[/color][/color]
page[color=blue]
> of[color=green]
> > the main form, the error went away.
> >
> > I now get data in the subform, but it is always only the same (single)
> > Person record as in the main form. That is, if I have a number of[/color]
> brothers[color=green]
> > and sisters, all with the same IDFather, only my own record is displayed[/color]
> in[color=green]
> > the subform. Which I eventually want to suppress, leaving only brothers[/color]
> and[color=green]
> > sisters. It seems that instead of the subforms Record Source being the
> > whole of the Person table, it is being restricted to the one record[/color]
> already[color=green]
> > selected by the main form. I had expected the VBA effectively to create[/color][/color]
a[color=blue][color=green]
> > new recordset (I have successfully used such recordsets in this manner
> > elsewhere in the main form VBA). How can I make the VBA for the subform
> > create a new Record Source based on the WHOLE of the Person table,[/color][/color]
rather[color=blue][color=green]
> > than the single record already selected by the main form?
> >
> > I feel that I am getting closer to the solution, there is just one piece[/color]
> of[color=green]
> > the puzzle still missing.
> >
> > --
> > Cheers,
> > Lyn.
> >
> > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> > news:40c6be3b$0$29803$5a62ac22@freenews.iinet.net. au...[color=darkred]
> > > Hi Lyn.
> > >
> > > To debug this, ask Access to print mySQL out to the immediate window:
> > > mySQL = "SELECT ...
> > > Debug.Print mySQL
> > > Me.[sfmSibs].Form...
> > >
> > > Now try it. Open the Immediate window (Ctrl+G). Access will have[/color][/color][/color]
printed[color=blue][color=green]
> > the[color=darkred]
> > > faulty SQL statement there. Copy it to clipboard. Then create a new[/color][/color]
> query,[color=green][color=darkred]
> > > switch it to SQL View (View menu), and paste in the faulty SQL string.[/color]
> > This[color=darkred]
> > > should help you to see what's wrong, and you will see how to fix the
> > > assigment to mySQL.
> > >
> > > The pipe character is a place holder for the field name. It appears[/color][/color][/color]
that[color=blue][color=green]
> > you[color=darkred]
> > > are getting the error message without the correct name being inserted[/color][/color]
> into[color=green][color=darkred]
> > > the place holder. Ultimately it means that Access can't make sense of[/color][/color]
> the[color=green][color=darkred]
> > > SQL statement. For example, if IDFather is actually a Text field (not[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> > > Number field), then the SQL statement needs exta quote marks:
> > > "WHERE Person.IDFather = """ & Me![IDFather] & """;"
> > >
> > > Another cause of the error is Name AutoCorrect incorrectly tracking[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > field name. Uncheck the boxes under Tools | Options | General | Name
> > > AutoCorrect, and then compact the database (Tools | Database[/color][/color][/color]
Utilities).[color=blue][color=green][color=darkred]
> > >
> > > You're on the right track, so it is worth continuing to debug this. Do
> > > include the trailing semicolon in the sql statement.
> > >
> > > It would be possible to use the Change event of the tab control (not[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > Click event of the page) rather than the Current event of the main[/color][/color][/color]
form.[color=blue][color=green]
> > The[color=darkred]
> > > problem with that approach is that it leaves the wrong data showing in[/color][/color]
> the[color=green][color=darkred]
> > > subform when you change record in the main form, until you move to a
> > > different tab and move back to the one you want again.
> > >
> > > --
> > > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > > Tips for Access users - http://allenbrowne.com/tips.html
> > > Reply to group, rather than allenbrowne at mvps dot org.
> > >
> > > "Lyn" <lhancock@ihug.com.au> wrote in message
> > > news:ca68e0$j3b$1@lust.ihug.co.nz...
> > > > Allen,
> > > > I have tried your suggestion and variations of it, but no luck.[/color][/color][/color]
After[color=blue]
> a[color=green][color=darkred]
> > > lot
> > > > of trial and error, I have resorted to using VBA code similar to[/color][/color][/color]
what[color=blue][color=green]
> > you[color=darkred]
> > > > provided in your previous. I have simplified the requirements to[/color][/color][/color]
the[color=blue][color=green]
> > bare[color=darkred]
> > > > minimum, just to get something that works, then I can build it up[/color][/color]
> again.[color=green][color=darkred]
> > > >
> > > > I now have a form containing a subform (sfmSibs) in a tab control.[/color][/color][/color]
I[color=blue][color=green]
> > have[color=darkred]
> > > > removed any query from the subform's Record Source.
> > > >
> > > > In the Current event for the main form, which I already use to[/color][/color]
> populate[color=green][color=darkred]
> > > some
> > > > unbound controls on the main form, I have added the following code:
> > > >
> > > > Dim mySQL As String
> > > >
> > > > If Not IsNull(Me![IDFather]) And Me![IDFather] <> 0 Then
> > > > mySQL = "SELECT Person.SurnameBirth, Person.FirstName, "[/color][/color][/color]
&[color=blue]
> _[color=green][color=darkred]
> > > > "Person.DOB, Person.Sex, " & _
> > > > "Person.IDPerson FROM Person " & _
> > > > "WHERE Person.IDFather = " & Me![IDFather] & ";"
> > > > Me.[sfmSibs].Form.RecordSource = mySQL
> > > > End If
> > > >
> > > > When I run this, if Me![IDFather] is null or zero, of course it[/color][/color][/color]
works[color=blue][color=green][color=darkred]
> > > (blank
> > > > form as expected). However, any record with a non-zero[/color][/color][/color]
Me![IDFather][color=blue][color=green][color=darkred]
> > > gives
> > > > this error:
> > > >
> > > > Run-time error '2465':
> > > > Microsoft Office Access can't find the field "|"
> > > > referred to in your expression.
> > > >
> > > > Not very helpful, as I don't have "|" anywhere in the expression.[/color][/color]
> (Once[color=green][color=darkred]
> > > > when I mistyped a word, this error message displayed the word in[/color][/color]
> error,[color=green][color=darkred]
> > > but
> > > > not this time.)
> > > >
> > > > When I click Debug, the following line is highlighted:
> > > >
> > > > Me.[sfmSibs].Form.RecordSource = mySQL
> > > >
> > > > I presume that the problem is somewhere in the mySQL string. I have[/color]
> > tried[color=darkred]
> > > > it with and without the terminating semicolon ";" (VBA often seems[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > insert
> > > > that for you), but no effect.
> > > >
> > > > Any ideas how to troubleshoot this when the field in error is quoted[/color][/color]
> as[color=green][color=darkred]
> > > "|"
> > > > ?!?!
> > > >
> > > > A related question: instead of doing this in the Current event of[/color][/color][/color]
the[color=blue][color=green]
> > main[color=darkred]
> > > > form, could I do it in the On Click event of the tab control Page?[/color][/color][/color]
So[color=blue][color=green][color=darkred]
> > > that
> > > > the subform is not populated unless the relevant tab is clicked.[/color][/color][/color]
Just[color=blue][color=green][color=darkred]
> > > > trying to learn as much as I can from this.
> > > >
> > > > Thanks again for your help.
> > > >
> > > > --
> > > > Cheers,
> > > > Lyn.[/color][/color]
>
>[/color]

Assuming that IDFather and IDPerson are text boxes in the main form, that
statement *should* work if you assign it to the RecordSource of the subform
in the Current event of the main form.

If you found a RecordSource property but not a SourceObject property, you
were looking at the form in the subform control, not the subform control
itself. Open the main form in design view. Right-click the edge of the
subform control, and choose Properties. SourceObject should be the first
item on the Data tab.

Hope that helps.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Lyn" <lhancock@ihug.com.au> wrote in message
news:ca9mjv$36q$1@lust.ihug.co.nz...[color=blue]
> Allen,
> Thanks again for the help and encouragement. And patience!
>
> I did not fully understand your previous suggestions. I have had queries
> and subforms with the IDFather value hardcoded working from the beginning.
> The problem has been in getting the query and subform integrated into the
> main form, using a value in the main form in place of the hardcoded value,
> and using the full table to select from instead of just the current record
> in the main form. I tried creating an alias of the table in the SQL by
> using the syntax:
>
> SELECT * FROM table AS alias WHERE alias.xxx = ...
>
> But this gave me the same result as not using the alias. That is, it just
> seems to give the record already selected in the main form a new table[/color]
name.[color=blue]
> What is needed is the alias to reflect a new copy of the whole table.[/color]
Maybe[color=blue]
> I am not applying the alias properly, although this was all I could find[/color]
in[color=blue]
> the Help file.
>
> I could not find a SourceObject property in the subform control. Only
> RecordSource, which points to the dummy query used when first opening the
> form.
>
> However, I do have this bit of code working, using the Immediate Pane as a
> pseudo subform:
>
> Dim Conn As ADODB.Connection
> Set Conn = CurrentProject.Connection
> Dim RS As New ADODB.Recordset
>
> If Not IsNull(Me![IDFather]) And Me![IDFather] <> 0 Then
> mySQL = "SELECT Person.Surname, Person.FirstName, Person.DOB,
> Person.Sex, " & _
> "Person.IDPerson FROM Person " & _
> "WHERE Person.IDFather = " & Me![IDFather] & _
> " AND Person.IDPerson <> " & Me![IDPerson] & ";"
> RS.Open mySQL, Conn, adOpenStatic
> If RS.RecordCount > 0 Then
> RS.MoveFirst
> While Not RS.EOF
> Debug.Print "Name: " & RS!Surname & " " & RS!FirstName
> RS.MoveNext
> Wend
> End If
> RS.Close
> End If
>
> This displays all the person's siblings in the debug pane -- ie, all[/color]
records[color=blue]
> in the Person table with the same IDFather value, excluding the record
> currently displayed in the main form.
>
> All I have to do now is find out how to display the same results in the
> subform instead of the debug window. A simple task! (You would think?)[/color]
So[color=blue]
> what am I still doing wrong?
>
> --
> Cheers,
> Lyn.
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:40c82049$0$29752$5a62ac22@freenews.iinet.net. au...[color=green]
> > Good: you are making progress with debugging.
> >
> > To change which form gets loaded into your subform control without[/color][/color]
running[color=blue][color=green]
> > the wizard, you could changethe SourceObject of the subform control.
> >
> > To help you get the desired SQL statement, create a new query and enter[/color]
> some[color=green]
> > dummy values for the person fields. If you have multiple copies of the
> > Person table, use an alias (properties box) to help assign a meaningful
> > name. When the query is working the way you need, switch it to SQL View
> > (View menu), and you have a sample of the SQL string you need to create.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Lyn" <lhancock@ihug.com.au> wrote in message
> > news:ca90fq$f0e$1@lust.ihug.co.nz...[color=darkred]
> > > Thanks again, Allen. This has given me some good debugging tools.
> > >
> > > I did as you suggested, all steps, but without any improvement.
> > >
> > > However, I did discover the main problem. Rather than continually[/color][/color]
> update[color=green][color=darkred]
> > > the same subform and same query, I had created new versions of each.[/color][/color][/color]
In[color=blue][color=green]
> > the[color=darkred]
> > > end I had about eight versions of the subform and a similar number of
> > > queries. Updating the query version of a form or subform is easy --[/color][/color][/color]
you[color=blue][color=green][color=darkred]
> > > just update the Record Source property. But I could not find any[/color][/color]
> analogue[color=green][color=darkred]
> > > of this for updating the subform version in the Form properties. I
> > > suspected that I might have had the wrong subform version configured[/color][/color][/color]
in[color=blue][color=green]
> > the[color=darkred]
> > > main form. The only way I could find to fix this was to delete the[/color]
> > current[color=darkred]
> > > subform and to recreate it from the Toolbox so that I could get into[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > Subform Wizard. Is there any other way to invoke the Subform Wizard?
> > >
> > > Anyway, after deleting and recreating the subform in the tab control[/color][/color]
> page[color=green]
> > of[color=darkred]
> > > the main form, the error went away.
> > >
> > > I now get data in the subform, but it is always only the same (single)
> > > Person record as in the main form. That is, if I have a number of[/color]
> > brothers[color=darkred]
> > > and sisters, all with the same IDFather, only my own record is[/color][/color][/color]
displayed[color=blue][color=green]
> > in[color=darkred]
> > > the subform. Which I eventually want to suppress, leaving only[/color][/color][/color]
brothers[color=blue][color=green]
> > and[color=darkred]
> > > sisters. It seems that instead of the subforms Record Source being[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > whole of the Person table, it is being restricted to the one record[/color]
> > already[color=darkred]
> > > selected by the main form. I had expected the VBA effectively to[/color][/color][/color]
create[color=blue]
> a[color=green][color=darkred]
> > > new recordset (I have successfully used such recordsets in this manner
> > > elsewhere in the main form VBA). How can I make the VBA for the[/color][/color][/color]
subform[color=blue][color=green][color=darkred]
> > > create a new Record Source based on the WHOLE of the Person table,[/color][/color]
> rather[color=green][color=darkred]
> > > than the single record already selected by the main form?
> > >
> > > I feel that I am getting closer to the solution, there is just one[/color][/color][/color]
piece[color=blue][color=green]
> > of[color=darkred]
> > > the puzzle still missing.
> > >
> > > --
> > > Cheers,
> > > Lyn.
> > >
> > > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> > > news:40c6be3b$0$29803$5a62ac22@freenews.iinet.net. au...
> > > > Hi Lyn.
> > > >
> > > > To debug this, ask Access to print mySQL out to the immediate[/color][/color][/color]
window:[color=blue][color=green][color=darkred]
> > > > mySQL = "SELECT ...
> > > > Debug.Print mySQL
> > > > Me.[sfmSibs].Form...
> > > >
> > > > Now try it. Open the Immediate window (Ctrl+G). Access will have[/color][/color]
> printed[color=green][color=darkred]
> > > the
> > > > faulty SQL statement there. Copy it to clipboard. Then create a new[/color]
> > query,[color=darkred]
> > > > switch it to SQL View (View menu), and paste in the faulty SQL[/color][/color][/color]
string.[color=blue][color=green][color=darkred]
> > > This
> > > > should help you to see what's wrong, and you will see how to fix the
> > > > assigment to mySQL.
> > > >
> > > > The pipe character is a place holder for the field name. It appears[/color][/color]
> that[color=green][color=darkred]
> > > you
> > > > are getting the error message without the correct name being[/color][/color][/color]
inserted[color=blue][color=green]
> > into[color=darkred]
> > > > the place holder. Ultimately it means that Access can't make sense[/color][/color][/color]
of[color=blue][color=green]
> > the[color=darkred]
> > > > SQL statement. For example, if IDFather is actually a Text field[/color][/color][/color]
(not[color=blue]
> a[color=green][color=darkred]
> > > > Number field), then the SQL statement needs exta quote marks:
> > > > "WHERE Person.IDFather = """ & Me![IDFather] & """;"
> > > >
> > > > Another cause of the error is Name AutoCorrect incorrectly tracking[/color][/color]
> the[color=green][color=darkred]
> > > > field name. Uncheck the boxes under Tools | Options | General | Name
> > > > AutoCorrect, and then compact the database (Tools | Database[/color][/color]
> Utilities).[color=green][color=darkred]
> > > >
> > > > You're on the right track, so it is worth continuing to debug this.[/color][/color][/color]
Do[color=blue][color=green][color=darkred]
> > > > include the trailing semicolon in the sql statement.
> > > >
> > > > It would be possible to use the Change event of the tab control (not[/color][/color]
> the[color=green][color=darkred]
> > > > Click event of the page) rather than the Current event of the main[/color][/color]
> form.[color=green][color=darkred]
> > > The
> > > > problem with that approach is that it leaves the wrong data showing[/color][/color][/color]
in[color=blue][color=green]
> > the[color=darkred]
> > > > subform when you change record in the main form, until you move to a
> > > > different tab and move back to the one you want again.
> > > >
> > > > --
> > > > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > > > Tips for Access users - http://allenbrowne.com/tips.html
> > > > Reply to group, rather than allenbrowne at mvps dot org.
> > > >
> > > > "Lyn" <lhancock@ihug.com.au> wrote in message
> > > > news:ca68e0$j3b$1@lust.ihug.co.nz...
> > > > > Allen,
> > > > > I have tried your suggestion and variations of it, but no luck.[/color][/color]
> After[color=green]
> > a[color=darkred]
> > > > lot
> > > > > of trial and error, I have resorted to using VBA code similar to[/color][/color]
> what[color=green][color=darkred]
> > > you
> > > > > provided in your previous. I have simplified the requirements to[/color][/color]
> the[color=green][color=darkred]
> > > bare
> > > > > minimum, just to get something that works, then I can build it up[/color]
> > again.[color=darkred]
> > > > >
> > > > > I now have a form containing a subform (sfmSibs) in a tab control.[/color][/color]
> I[color=green][color=darkred]
> > > have
> > > > > removed any query from the subform's Record Source.
> > > > >
> > > > > In the Current event for the main form, which I already use to[/color]
> > populate[color=darkred]
> > > > some
> > > > > unbound controls on the main form, I have added the following[/color][/color][/color]
code:[color=blue][color=green][color=darkred]
> > > > >
> > > > > Dim mySQL As String
> > > > >
> > > > > If Not IsNull(Me![IDFather]) And Me![IDFather] <> 0 Then
> > > > > mySQL = "SELECT Person.SurnameBirth, Person.FirstName,[/color][/color][/color]
"[color=blue]
> &[color=green]
> > _[color=darkred]
> > > > > "Person.DOB, Person.Sex, " & _
> > > > > "Person.IDPerson FROM Person " & _
> > > > > "WHERE Person.IDFather = " & Me![IDFather] &[/color][/color][/color]
";"[color=blue][color=green][color=darkred]
> > > > > Me.[sfmSibs].Form.RecordSource = mySQL
> > > > > End If
> > > > >
> > > > > When I run this, if Me![IDFather] is null or zero, of course it[/color][/color]
> works[color=green][color=darkred]
> > > > (blank
> > > > > form as expected). However, any record with a non-zero[/color][/color]
> Me![IDFather][color=green][color=darkred]
> > > > gives
> > > > > this error:
> > > > >
> > > > > Run-time error '2465':
> > > > > Microsoft Office Access can't find the field "|"
> > > > > referred to in your expression.
> > > > >
> > > > > Not very helpful, as I don't have "|" anywhere in the expression.[/color]
> > (Once[color=darkred]
> > > > > when I mistyped a word, this error message displayed the word in[/color]
> > error,[color=darkred]
> > > > but
> > > > > not this time.)
> > > > >
> > > > > When I click Debug, the following line is highlighted:
> > > > >
> > > > > Me.[sfmSibs].Form.RecordSource = mySQL
> > > > >
> > > > > I presume that the problem is somewhere in the mySQL string. I[/color][/color][/color]
have[color=blue][color=green][color=darkred]
> > > tried
> > > > > it with and without the terminating semicolon ";" (VBA often seems[/color][/color]
> to[color=green][color=darkred]
> > > > insert
> > > > > that for you), but no effect.
> > > > >
> > > > > Any ideas how to troubleshoot this when the field in error is[/color][/color][/color]
quoted[color=blue][color=green]
> > as[color=darkred]
> > > > "|"
> > > > > ?!?!
> > > > >
> > > > > A related question: instead of doing this in the Current event of[/color][/color]
> the[color=green][color=darkred]
> > > main
> > > > > form, could I do it in the On Click event of the tab control Page?[/color][/color]
> So[color=green][color=darkred]
> > > > that
> > > > > the subform is not populated unless the relevant tab is clicked.[/color][/color]
> Just[color=green][color=darkred]
> > > > > trying to learn as much as I can from this.
> > > > >
> > > > > Thanks again for your help.
> > > > >
> > > > > --
> > > > > Cheers,
> > > > > Lyn.[/color][/color][/color]

Allen, thanks for the prompt response. With your guidance, I did find
Source Object -- thank you.

Re assigning "that statement" to the subform Record Source in the main form
Current event, did you mean the code I had defining the recordset, but using
"Me.[sfmSibs].Form.RecordSource = mySQL" instead of the "RS.Open ..."? Or
did you mean use it with the SQL statement "SELECT * FROM table AS alias
WHERE alias.xxx = ..."?

I am pretty sure that I tried the former before, but I am so confused with
all the things I tried that I am prepared to try it again.

--
Cheers,
Lyn.

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:40c85fe4$0$29811$5a62ac22@freenews.iinet.net. au...[color=blue]
> Assuming that IDFather and IDPerson are text boxes in the main form, that
> statement *should* work if you assign it to the RecordSource of the[/color]
subform[color=blue]
> in the Current event of the main form.
>
> If you found a RecordSource property but not a SourceObject property, you
> were looking at the form in the subform control, not the subform control
> itself. Open the main form in design view. Right-click the edge of the
> subform control, and choose Properties. SourceObject should be the first
> item on the Data tab.
>
> Hope that helps.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> "Lyn" <lhancock@ihug.com.au> wrote in message
> news:ca9mjv$36q$1@lust.ihug.co.nz...[color=green]
> > Allen,
> > Thanks again for the help and encouragement. And patience!
> >
> > I did not fully understand your previous suggestions. I have had[/color][/color]
queries[color=blue][color=green]
> > and subforms with the IDFather value hardcoded working from the[/color][/color]
beginning.[color=blue][color=green]
> > The problem has been in getting the query and subform integrated into[/color][/color]
the[color=blue][color=green]
> > main form, using a value in the main form in place of the hardcoded[/color][/color]
value,[color=blue][color=green]
> > and using the full table to select from instead of just the current[/color][/color]
record[color=blue][color=green]
> > in the main form. I tried creating an alias of the table in the SQL by
> > using the syntax:
> >
> > SELECT * FROM table AS alias WHERE alias.xxx = ...
> >
> > But this gave me the same result as not using the alias. That is, it[/color][/color]
just[color=blue][color=green]
> > seems to give the record already selected in the main form a new table[/color]
> name.[color=green]
> > What is needed is the alias to reflect a new copy of the whole table.[/color]
> Maybe[color=green]
> > I am not applying the alias properly, although this was all I could find[/color]
> in[color=green]
> > the Help file.
> >
> > I could not find a SourceObject property in the subform control. Only
> > RecordSource, which points to the dummy query used when first opening[/color][/color]
the[color=blue][color=green]
> > form.
> >
> > However, I do have this bit of code working, using the Immediate Pane as[/color][/color]
a[color=blue][color=green]
> > pseudo subform:
> >
> > Dim Conn As ADODB.Connection
> > Set Conn = CurrentProject.Connection
> > Dim RS As New ADODB.Recordset
> >
> > If Not IsNull(Me![IDFather]) And Me![IDFather] <> 0 Then
> > mySQL = "SELECT Person.Surname, Person.FirstName, Person.DOB,
> > Person.Sex, " & _
> > "Person.IDPerson FROM Person " & _
> > "WHERE Person.IDFather = " & Me![IDFather] & _
> > " AND Person.IDPerson <> " & Me![IDPerson] & ";"
> > RS.Open mySQL, Conn, adOpenStatic
> > If RS.RecordCount > 0 Then
> > RS.MoveFirst
> > While Not RS.EOF
> > Debug.Print "Name: " & RS!Surname & " " & RS!FirstName
> > RS.MoveNext
> > Wend
> > End If
> > RS.Close
> > End If
> >
> > This displays all the person's siblings in the debug pane -- ie, all[/color]
> records[color=green]
> > in the Person table with the same IDFather value, excluding the record
> > currently displayed in the main form.
> >
> > All I have to do now is find out how to display the same results in the
> > subform instead of the debug window. A simple task! (You would think?)[/color]
> So[color=green]
> > what am I still doing wrong?
> >
> > --
> > Cheers,
> > Lyn.
> >[/color][/color]

Yes. Now you have a working query statement, you should be able to do this:
Me.[sfmSibs].Form.RecordSource = mySQL

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lyn" <lhancock@ihug.com.au> wrote in message
news:ca9piu$5pc$1@lust.ihug.co.nz...[color=blue]
> Allen, thanks for the prompt response. With your guidance, I did find
> Source Object -- thank you.
>
> Re assigning "that statement" to the subform Record Source in the main[/color]
form[color=blue]
> Current event, did you mean the code I had defining the recordset, but[/color]
using[color=blue]
> "Me.[sfmSibs].Form.RecordSource = mySQL" instead of the "RS.Open ..."? Or
> did you mean use it with the SQL statement "SELECT * FROM table AS alias
> WHERE alias.xxx = ..."?
>
> I am pretty sure that I tried the former before, but I am so confused with
> all the things I tried that I am prepared to try it again.
>
> --
> Cheers,
> Lyn.[/color]

Allen, still no go. Still selects only the current record from the main
form.

I'd like to recap and summarise the situation as I understand it now. This
is a very simplified description of the form and associated code for ease of
understanding.

1) I have a form (main form) which displays one record of a family member
from the table Person. Each such record has a unique (autonum) ID field --
IDPerson. Each record also has a non-unique field -- IDFather.

2) I have a subform (sfmSibs) in the main form in which I wish to display
all records in the Person table which have the same IDFather value as that
of the current record in the main form.

3) Consider the following SQL which will be used in both points 4) and 5)
below:

Dim mySQL As String
mySQL = "SELECT * FROM Table WHERE Person.IDFather = " & Me![IDFather] &
";"

"Me![IDFather]" is a control on the main form where the current record's
IDFather value is displayed.

4) The following code creates a recordset which includes ALL of the Person
records with the current value of Me![IDFather]:

Dim RS As New ADODB.Recordset
Dim Conn As ADODB.Connection
Set Conn = CurrentProject.Connection

RS.Open mySQL, Conn, adOpenStatic

From this recordset, I can access all of the records with the same IDFather,
but cannot (as yet) find a way to display the recordset in the subform.

5) The following alternative code will display in the subform:

Me.[sfmSibs].Form.RecordSource = mySQL

However, mySQL in this case selects ONLY from the single Person record which
is already displayed in the main form (or so it seems). That is, the
subform never contains more than one row which is the same as the main form
record, regardless of how many records contain the same IDFather value.

What I need to do is somehow combine points 4) and 5). That is, effectively
get the subform to display a recordset compiled from the entire Person
table, not just the current main form record. I have tried combining these
steps, by specifying RS in various ways as the subform record source, but
with no success.

I can't help thinking that part of the recordset process (point 4) is to
specify a connection to the database (ie: the "Conn" argument in the RS.Open
statement). Maybe this is what causes the recordset to start with a fresh
(full) copy of the table. The code in point 5) stands on its own, and since
the main form has already selected a single Person record, the code seems to
take that as its source rather than starting with a fresh copy of the whole
table.

At least that is how it looks to me. Is there something missing from the
point 5) code? What do I need to do to get the SQL to select from the WHOLE
of the Person table for the subform?

--
Cheers,
Lyn.

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:40c86fe7$0$29754$5a62ac22@freenews.iinet.net. au...[color=blue]
> Yes. Now you have a working query statement, you should be able to do[/color]
this:[color=blue]
> Me.[sfmSibs].Form.RecordSource = mySQL
>
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Lyn" <lhancock@ihug.com.au> wrote in message
> news:ca9piu$5pc$1@lust.ihug.co.nz...[color=green]
> > Allen, thanks for the prompt response. With your guidance, I did find
> > Source Object -- thank you.
> >
> > Re assigning "that statement" to the subform Record Source in the main[/color]
> form[color=green]
> > Current event, did you mean the code I had defining the recordset, but[/color]
> using[color=green]
> > "Me.[sfmSibs].Form.RecordSource = mySQL" instead of the "RS.Open ..."?[/color][/color]
Or[color=blue][color=green]
> > did you mean use it with the SQL statement "SELECT * FROM table AS alias
> > WHERE alias.xxx = ..."?
> >
> > I am pretty sure that I tried the former before, but I am so confused[/color][/color]
with[color=blue][color=green]
> > all the things I tried that I am prepared to try it again.
> >
> > --
> > Cheers,
> > Lyn.[/color]
>
>[/color]

Lyn, I'm guessing that Access is automatically setting the
LinkMasterFields/LinkChildFields properties of the subform control. You
don't want that, because you are programmatically assigning the records.

Open the main form in design view.
Right-click the edge of the subform control, and choose Properties.
On the Data tab of the Properties box, locate the LinkMasterFields control,
and delete the IDPerson entry. Delete the entry for LinkChildFields as well.

I'm going to be away for the weekend, but will check back on Tuesday to see
if this solved your issue.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lyn" <lhancock@ihug.com.au> wrote in message
news:cabk1k$8qi$1@lust.ihug.co.nz...[color=blue]
> Allen, still no go. Still selects only the current record from the main
> form.
>
> I'd like to recap and summarise the situation as I understand it now.[/color]
This[color=blue]
> is a very simplified description of the form and associated code for ease[/color]
of[color=blue]
> understanding.
>
> 1) I have a form (main form) which displays one record of a family member
> from the table Person. Each such record has a unique (autonum) ID[/color]
field --[color=blue]
> IDPerson. Each record also has a non-unique field -- IDFather.
>
> 2) I have a subform (sfmSibs) in the main form in which I wish to display
> all records in the Person table which have the same IDFather value as that
> of the current record in the main form.
>
> 3) Consider the following SQL which will be used in both points 4) and 5)
> below:
>
> Dim mySQL As String
> mySQL = "SELECT * FROM Table WHERE Person.IDFather = " & Me![IDFather][/color]
&[color=blue]
> ";"
>
> "Me![IDFather]" is a control on the main form where the current record's
> IDFather value is displayed.
>
> 4) The following code creates a recordset which includes ALL of the Person
> records with the current value of Me![IDFather]:
>
> Dim RS As New ADODB.Recordset
> Dim Conn As ADODB.Connection
> Set Conn = CurrentProject.Connection
>
> RS.Open mySQL, Conn, adOpenStatic
>
> From this recordset, I can access all of the records with the same[/color]
IDFather,[color=blue]
> but cannot (as yet) find a way to display the recordset in the subform.
>
> 5) The following alternative code will display in the subform:
>
> Me.[sfmSibs].Form.RecordSource = mySQL
>
> However, mySQL in this case selects ONLY from the single Person record[/color]
which[color=blue]
> is already displayed in the main form (or so it seems). That is, the
> subform never contains more than one row which is the same as the main[/color]
form[color=blue]
> record, regardless of how many records contain the same IDFather value.
>
>
> What I need to do is somehow combine points 4) and 5). That is,[/color]
effectively[color=blue]
> get the subform to display a recordset compiled from the entire Person
> table, not just the current main form record. I have tried combining[/color]
these[color=blue]
> steps, by specifying RS in various ways as the subform record source, but
> with no success.
>
> I can't help thinking that part of the recordset process (point 4) is to
> specify a connection to the database (ie: the "Conn" argument in the[/color]
RS.Open[color=blue]
> statement). Maybe this is what causes the recordset to start with a fresh
> (full) copy of the table. The code in point 5) stands on its own, and[/color]
since[color=blue]
> the main form has already selected a single Person record, the code seems[/color]
to[color=blue]
> take that as its source rather than starting with a fresh copy of the[/color]
whole[color=blue]
> table.
>
> At least that is how it looks to me. Is there something missing from the
> point 5) code? What do I need to do to get the SQL to select from the[/color]
WHOLE[color=blue]
> of the Person table for the subform?
>
> --
> Cheers,
> Lyn.
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:40c86fe7$0$29754$5a62ac22@freenews.iinet.net. au...[color=green]
> > Yes. Now you have a working query statement, you should be able to do[/color]
> this:[color=green]
> > Me.[sfmSibs].Form.RecordSource = mySQL
> >
> >
> > "Lyn" <lhancock@ihug.com.au> wrote in message
> > news:ca9piu$5pc$1@lust.ihug.co.nz...[color=darkred]
> > > Allen, thanks for the prompt response. With your guidance, I did find
> > > Source Object -- thank you.
> > >
> > > Re assigning "that statement" to the subform Record Source in the main[/color]
> > form[color=darkred]
> > > Current event, did you mean the code I had defining the recordset, but[/color]
> > using[color=darkred]
> > > "Me.[sfmSibs].Form.RecordSource = mySQL" instead of the "RS.Open ..."?[/color][/color]
> Or[color=green][color=darkred]
> > > did you mean use it with the SQL statement "SELECT * FROM table AS[/color][/color][/color]
alias[color=blue][color=green][color=darkred]
> > > WHERE alias.xxx = ..."?
> > >
> > > I am pretty sure that I tried the former before, but I am so confused[/color][/color]
> with[color=green][color=darkred]
> > > all the things I tried that I am prepared to try it again.
> > >
> > > --
> > > Cheers,
> > > Lyn.[/color][/color][/color]

Allen, I hope you enjoyed your break.

I have had no luck with the VBA, but Helen has emailed me a solution which
uses neither VBA nor a query (thanks again Helen). It just inserts an SQL
Select statement directly into the RecordSource of the subform. And it
works fine. The statement is:

SELECT * FROM Person WHERE
((Person.IDFather=[Forms]![frmMainForm]![IDFather]) OR
(Person.IDMother=[Forms]![frmMainForm]![IDMother]))
AND (Not (Person.IDperson=[Forms]![frmMainForm]![IDperson]));

While I hadn't thought of inserting the SQL directly into RecordSource, I
did think that I had tried this as a Query. However, I have tried so many
different things trying to resolve this that I can't be sure. In the end,
it was certainly simple enough.

I can't help thinking that the solution via VBA is going to be simple too.
Having come this far, I would still like to find the VBA solution, if only
as a learning exercise. So if you still have the patience, I would like to
continue.

One other thing. Rather than displaying the IDFather and IDMother values in
the Continuous Feeds subform, I would like to translate each into a text
field inserted into the row (eg, [FirstName] & " " & [Surname]). Using
Helen's solution, I have created a text control in the subform row for
Father. In the RecordSource for the control, I have firstly inserted a
simple SQL statement (which didn't work -- gave me "#Name?"), and then put
the same SQL into a Query (qryFindFatherName) and specified the query as the
RecordSource for the control (with the same result). The query was:

SELECT * FROM Person WHERE Person.IDPerson=Forms!sfmSibs!IDFather;

This query works well enough when run directly as a query (I get prompted to
input the "Forms!sfmSibs!IDFather"), but not when run from the main form. I
have also tried variations such as "[Forms].[sfmSibs]![IDFather]" in the
query with the same result.

In the RecordSource expression builder, I have:
"=qryFindFatherName!FirstName & " " & qryFindFatherName!Surname.

I think that the problem is that I am not correctly specifying the control
for IDFather in the subform row for the WHERE statement in the query. What
do you think? Helen, please feel free to comment also.

Thanks again.

--
Cheers,
Lyn.

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:40c99b29$0$24181$5a62ac22@freenews.iinet.net. au...[color=blue]
> Lyn, I'm guessing that Access is automatically setting the
> LinkMasterFields/LinkChildFields properties of the subform control. You
> don't want that, because you are programmatically assigning the records.
>
> Open the main form in design view.
> Right-click the edge of the subform control, and choose Properties.
> On the Data tab of the Properties box, locate the LinkMasterFields[/color]
control,[color=blue]
> and delete the IDPerson entry. Delete the entry for LinkChildFields as[/color]
well.[color=blue]
>
> I'm going to be away for the weekend, but will check back on Tuesday to[/color]
see[color=blue]
> if this solved your issue.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>[/color]

Hi Lyn

Just back, and have had a chance to try what you are doing. The problem you
are seeing is a bug in Access.

Although you have saved the form with *nothing* in the
LinkMasterFields/LinkChildFields of your subform, Access is WRONGLY
assigning values to these properties when you assign the RecordSource of the
subform. The result is that the only subform record that can match your main
form record is itself! This is completely the wrong result for what you want
(i.e. all the *other* siblings except the person themself).

Even if you clear the automatic assignment to these properties, Access will
create the assignment again as soon as you move record in the main form.

There is a sample of this bug at this address:
http://allenbrowne.com/BadSubform.zip
I think you will find that this is exactly the behaviour you have been
struggling with.

You can work around the behaviour by explicitly reassigning an empty string
to the subform's LinkMasterFields and LinkChildFields properties in the main
form's Current event, after you assign the RecordSource to the subform. It's
an extremely inelegant workaround, but it does appear to let you have the
sibblings in your subform.

Looks like another Access bug that needs to be written up.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lyn" <lhancock@ihug.com.au> wrote in message
news:cajg57$73p$1@lust.ihug.co.nz...[color=blue]
> Allen, I hope you enjoyed your break.
>
> I have had no luck with the VBA, but Helen has emailed me a solution which
> uses neither VBA nor a query (thanks again Helen). It just inserts an SQL
> Select statement directly into the RecordSource of the subform. And it
> works fine. The statement is:
>
> SELECT * FROM Person WHERE
> ((Person.IDFather=[Forms]![frmMainForm]![IDFather]) OR
> (Person.IDMother=[Forms]![frmMainForm]![IDMother]))
> AND (Not (Person.IDperson=[Forms]![frmMainForm]![IDperson]));
>
> While I hadn't thought of inserting the SQL directly into RecordSource, I
> did think that I had tried this as a Query. However, I have tried so many
> different things trying to resolve this that I can't be sure. In the end,
> it was certainly simple enough.
>
> I can't help thinking that the solution via VBA is going to be simple too.
> Having come this far, I would still like to find the VBA solution, if only
> as a learning exercise. So if you still have the patience, I would like[/color]
to[color=blue]
> continue.
>
> One other thing. Rather than displaying the IDFather and IDMother values[/color]
in[color=blue]
> the Continuous Feeds subform, I would like to translate each into a text
> field inserted into the row (eg, [FirstName] & " " & [Surname]). Using
> Helen's solution, I have created a text control in the subform row for
> Father. In the RecordSource for the control, I have firstly inserted a
> simple SQL statement (which didn't work -- gave me "#Name?"), and then put
> the same SQL into a Query (qryFindFatherName) and specified the query as[/color]
the[color=blue]
> RecordSource for the control (with the same result). The query was:
>
> SELECT * FROM Person WHERE Person.IDPerson=Forms!sfmSibs!IDFather;
>
> This query works well enough when run directly as a query (I get prompted[/color]
to[color=blue]
> input the "Forms!sfmSibs!IDFather"), but not when run from the main form.[/color]
I[color=blue]
> have also tried variations such as "[Forms].[sfmSibs]![IDFather]" in the
> query with the same result.
>
> In the RecordSource expression builder, I have:
> "=qryFindFatherName!FirstName & " " & qryFindFatherName!Surname.
>
> I think that the problem is that I am not correctly specifying the control
> for IDFather in the subform row for the WHERE statement in the query.[/color]
What[color=blue]
> do you think? Helen, please feel free to comment also.
>
> Thanks again.
>
> --
> Cheers,
> Lyn.
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:40c99b29$0$24181$5a62ac22@freenews.iinet.net. au...[color=green]
> > Lyn, I'm guessing that Access is automatically setting the
> > LinkMasterFields/LinkChildFields properties of the subform control. You
> > don't want that, because you are programmatically assigning the records.
> >
> > Open the main form in design view.
> > Right-click the edge of the subform control, and choose Properties.
> > On the Data tab of the Properties box, locate the LinkMasterFields[/color]
> control,[color=green]
> > and delete the IDPerson entry. Delete the entry for LinkChildFields as[/color]
> well.[color=green]
> >
> > I'm going to be away for the weekend, but will check back on Tuesday to[/color]
> see[color=green]
> > if this solved your issue.
> >[/color][/color]

Getting there - more below!

Lyn wrote:[color=blue]
> Allen, I hope you enjoyed your break.
>
> I have had no luck with the VBA, but Helen has emailed me a solution which
> uses neither VBA nor a query (thanks again Helen). It just inserts an SQL
> Select statement directly into the RecordSource of the subform. And it
> works fine. The statement is:
>
> SELECT * FROM Person WHERE
> ((Person.IDFather=[Forms]![frmMainForm]![IDFather]) OR
> (Person.IDMother=[Forms]![frmMainForm]![IDMother]))
> AND (Not (Person.IDperson=[Forms]![frmMainForm]![IDperson]));
>
> While I hadn't thought of inserting the SQL directly into RecordSource, I
> did think that I had tried this as a Query. However, I have tried so many
> different things trying to resolve this that I can't be sure. In the end,
> it was certainly simple enough.
>
> I can't help thinking that the solution via VBA is going to be simple too.
> Having come this far, I would still like to find the VBA solution, if only
> as a learning exercise. So if you still have the patience, I would like to
> continue.[/color]

Yes, I'd agree that setting the subform's record source using VBA
code running on the main form's Current event is a better way to
go. Although the quick and dirty solution I sent you earlier
works, it's not very easily re-usable. What are the odds you'll
want to display grandchildren/nieces & nephews etc later? If you
do, you can re-use the same subform but set a different
recordsource for it from the main form.

So, combining some bits of mine and some bits of Allen's
responses, try putting the following in your main form's
OnCurrent event (watch out for possible line wrapping in your
mail reader and make sure I got the name of your subform right!):

'CODE STARTS HERE:
Dim mySQL As String

'Create the SQL to select people with either the same mother or
'the same father as the current record in the main form.
'We deal with null/unknown parent IDs by checking whether theyre
'equal to an ID number that no person will ever have, i.e. 0

mySQL = "SELECT * FROM Person " & _
"WHERE ((Person.IDFather=" & Nz(Me![IDFather], 0) & ") " & _
"OR (Person.IDMother=" & Nz(Me![IDMother], 0) & ")) " & _
"AND (Not (Person.IDperson=" & Me![IDperson] & "));"

'Assign that SQL string as the record source of the subform
Me![sfmSibs].Form.RecordSource = mySQL

'Deal with Access's rude habit of filling in the link
'master/child fields with values you didn't ask for:
Me![sfmSibs].LinkChildFields = ""
Me![sfmSibs].LinkMasterFields = ""

'CODE ENDS HERE

[color=blue]
>
> One other thing. Rather than displaying the IDFather and IDMother values in
> the Continuous Feeds subform, I would like to translate each into a text
> field inserted into the row (eg, [FirstName] & " " & [Surname]). Using
> Helen's solution, I have created a text control in the subform row for
> Father. In the RecordSource for the control, I have firstly inserted a
> simple SQL statement (which didn't work -- gave me "#Name?"), and then put
> the same SQL into a Query (qryFindFatherName) and specified the query as the
> RecordSource for the control (with the same result). The query was:
>[/color]

I'd probably join the person table to itself in the subform
record source, once to get the mother's details and a second time
for the father's. The SQL for the subform's recordsource would
then be something like:

mySQL = "SELECT Person.*, " & _
"father.name AS fathername, mother.name AS mothername " & _
"FROM (Person LEFT JOIN person AS father " & _
"ON Person.IDfather = father.idPerson) " & _
"LEFT JOIN person AS mother " & _
"ON Person.IDmother = mother.idPerson " & _
"WHERE ((Person.IDFather=" & Nz(Me![IDFather], 0) & ") " & _
"OR (Person.IDMother=" & Nz(Me![IDMother], 0) & ")) " & _
"AND (Not (Person.IDperson=" & Me![IDperson] & "));"

I'll bet I got at least one typo somewhere in the above, but
hopefully this gets you a bit further along the track. Good luck!
Helen

Brilliant, Allen!!!

You have hit the nail right on the head. I have added statements to the VBA
to blank out these fields after the new record has loaded (via the Current
event), and it works perfectly.

As a newbie to Access, I would never have found that!

Now, to finish off, have you any ideas about my other query?
[color=blue]
> One other thing. Rather than displaying the IDFather and IDMother
> values in the Continuous Feeds subform, I would like to translate
> each into a text field inserted into the row
> (eg, [FirstName] & " " & [Surname]). Using Helen's solution,
> I have created a text control in the subform row for Father.
> In the RecordSource for the control, I have firstly inserted a
> simple SQL statement (which didn't work -- gave me
> "#Name?"), and then put the same SQL into a Query
> (qryFindFatherName) and specified the query as the
> RecordSource for the control (with the same result).
> The query was:
>
> SELECT * FROM Person WHERE Person.IDPerson=Forms!sfmSibs!IDFather;
>
> This query works well enough when run directly as a query (I get prompted
> to input the "Forms!sfmSibs!IDFather"), but not when run from the
> main form. I have also tried variations such as[/color]
"[Forms].[sfmSibs]![IDFather]"[color=blue]
> in the query with the same result.
>
> In the RecordSource expression builder, I have:
> "=qryFindFatherName!FirstName & " " & qryFindFatherName!Surname.
>
> I think that the problem is that I am not correctly specifying the control
> for IDFather in the subform row for the WHERE statement in the query.
> What do you think?[/color]

A VBA solution would also be helpful as I also want to translate the Sex
value (Male/Female) to Relationship
(Brother/Half-Brother/Sister/Half-Sister) in the Siblings subform. Once I
know how to access the values returned in each row, translating Sex to
Relationship will be fairly simple VBA.

Many thanks again!
--
Cheers,
Lyn.

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:40cdad31$0$28941$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=blue]
> Hi Lyn
>
> Just back, and have had a chance to try what you are doing. The problem[/color]
you[color=blue]
> are seeing is a bug in Access.
>
> Although you have saved the form with *nothing* in the
> LinkMasterFields/LinkChildFields of your subform, Access is WRONGLY
> assigning values to these properties when you assign the RecordSource of[/color]
the[color=blue]
> subform. The result is that the only subform record that can match your[/color]
main[color=blue]
> form record is itself! This is completely the wrong result for what you[/color]
want[color=blue]
> (i.e. all the *other* siblings except the person themself).
>
> Even if you clear the automatic assignment to these properties, Access[/color]
will[color=blue]
> create the assignment again as soon as you move record in the main form.
>
> There is a sample of this bug at this address:
> http://allenbrowne.com/BadSubform.zip
> I think you will find that this is exactly the behaviour you have been
> struggling with.
>
> You can work around the behaviour by explicitly reassigning an empty[/color]
string[color=blue]
> to the subform's LinkMasterFields and LinkChildFields properties in the[/color]
main[color=blue]
> form's Current event, after you assign the RecordSource to the subform.[/color]
It's[color=blue]
> an extremely inelegant workaround, but it does appear to let you have the
> sibblings in your subform.
>
> Looks like another Access bug that needs to be written up.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Lyn" <lhancock@ihug.com.au> wrote in message
> news:cajg57$73p$1@lust.ihug.co.nz...[color=green]
> > Allen, I hope you enjoyed your break.
> >
> > I have had no luck with the VBA, but Helen has emailed me a solution[/color][/color]
which[color=blue][color=green]
> > uses neither VBA nor a query (thanks again Helen). It just inserts an[/color][/color]
SQL[color=blue][color=green]
> > Select statement directly into the RecordSource of the subform. And it
> > works fine. The statement is:
> >
> > SELECT * FROM Person WHERE
> > ((Person.IDFather=[Forms]![frmMainForm]![IDFather]) OR
> > (Person.IDMother=[Forms]![frmMainForm]![IDMother]))
> > AND (Not (Person.IDperson=[Forms]![frmMainForm]![IDperson]));
> >
> > While I hadn't thought of inserting the SQL directly into RecordSource,[/color][/color]
I[color=blue][color=green]
> > did think that I had tried this as a Query. However, I have tried so[/color][/color]
many[color=blue][color=green]
> > different things trying to resolve this that I can't be sure. In the[/color][/color]
end,[color=blue][color=green]
> > it was certainly simple enough.
> >
> > I can't help thinking that the solution via VBA is going to be simple[/color][/color]
too.[color=blue][color=green]
> > Having come this far, I would still like to find the VBA solution, if[/color][/color]
only[color=blue][color=green]
> > as a learning exercise. So if you still have the patience, I would like[/color]
> to[color=green]
> > continue.
> >
> > One other thing. Rather than displaying the IDFather and IDMother[/color][/color]
values[color=blue]
> in[color=green]
> > the Continuous Feeds subform, I would like to translate each into a text
> > field inserted into the row (eg, [FirstName] & " " & [Surname]). Using
> > Helen's solution, I have created a text control in the subform row for
> > Father. In the RecordSource for the control, I have firstly inserted a
> > simple SQL statement (which didn't work -- gave me "#Name?"), and then[/color][/color]
put[color=blue][color=green]
> > the same SQL into a Query (qryFindFatherName) and specified the query as[/color]
> the[color=green]
> > RecordSource for the control (with the same result). The query was:
> >
> > SELECT * FROM Person WHERE Person.IDPerson=Forms!sfmSibs!IDFather;
> >
> > This query works well enough when run directly as a query (I get[/color][/color]
prompted[color=blue]
> to[color=green]
> > input the "Forms!sfmSibs!IDFather"), but not when run from the main[/color][/color]
form.[color=blue]
> I[color=green]
> > have also tried variations such as "[Forms].[sfmSibs]![IDFather]" in the
> > query with the same result.
> >
> > In the RecordSource expression builder, I have:
> > "=qryFindFatherName!FirstName & " " & qryFindFatherName!Surname.
> >
> > I think that the problem is that I am not correctly specifying the[/color][/color]
control[color=blue][color=green]
> > for IDFather in the subform row for the WHERE statement in the query.[/color]
> What[color=green]
> > do you think? Helen, please feel free to comment also.
> >
> > Thanks again.
> >
> > --
> > Cheers,
> > Lyn.
> >
> > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> > news:40c99b29$0$24181$5a62ac22@freenews.iinet.net. au...[color=darkred]
> > > Lyn, I'm guessing that Access is automatically setting the
> > > LinkMasterFields/LinkChildFields properties of the subform control.[/color][/color][/color]
You[color=blue][color=green][color=darkred]
> > > don't want that, because you are programmatically assigning the[/color][/color][/color]
records.[color=blue][color=green][color=darkred]
> > >
> > > Open the main form in design view.
> > > Right-click the edge of the subform control, and choose Properties.
> > > On the Data tab of the Properties box, locate the LinkMasterFields[/color]
> > control,[color=darkred]
> > > and delete the IDPerson entry. Delete the entry for LinkChildFields as[/color]
> > well.[color=darkred]
> > >
> > > I'm going to be away for the weekend, but will check back on Tuesday[/color][/color][/color]
to[color=blue][color=green]
> > see[color=darkred]
> > > if this solved your issue.
> > >[/color][/color]
>
>[/color]

Hi Lyn

You should be able to use a query statement like this one:

SELECT tblPerson.*,
Trim([Father].[FirstName] & " " & [Father].[Surname]) AS FatherName,
Trim([Mother].[FirstName] & " " & [Mother].[Surname]) AS MotherName
FROM (tblPerson LEFT JOIN tblPerson AS Father ON tblPerson.IDFather =
Father.IDPerson)
LEFT JOIN tblPerson AS Mother ON tblPerson.IDMother = Mother.IDPerson;

You can paste that into SQL View of a query (View menu in query design), to
see how it works, and modify it to suit.

Essentially, the query contains 3 copies of the same table. Two copies are
aliased so they are meaningful. (Set an Alias in the Properties box - view
menu).

We use outer joins in case the parents are unknown. (Double-click the join
lines to set the join type.) Provided you are not trying to enter the
parents names, you should find that this query is updatable, so suitable for
your form.

I think Helen is suggesting something similar.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lyn" <lhancock@ihug.com.au> wrote in message
news:cam8pb$3bh$1@lust.ihug.co.nz...[color=blue]
> Brilliant, Allen!!!
>
> You have hit the nail right on the head. I have added statements to the[/color]
VBA[color=blue]
> to blank out these fields after the new record has loaded (via the Current
> event), and it works perfectly.
>
> As a newbie to Access, I would never have found that!
>
> Now, to finish off, have you any ideas about my other query?
>[color=green]
> > One other thing. Rather than displaying the IDFather and IDMother
> > values in the Continuous Feeds subform, I would like to translate
> > each into a text field inserted into the row
> > (eg, [FirstName] & " " & [Surname]). Using Helen's solution,
> > I have created a text control in the subform row for Father.
> > In the RecordSource for the control, I have firstly inserted a
> > simple SQL statement (which didn't work -- gave me
> > "#Name?"), and then put the same SQL into a Query
> > (qryFindFatherName) and specified the query as the
> > RecordSource for the control (with the same result).
> > The query was:
> >
> > SELECT * FROM Person WHERE Person.IDPerson=Forms!sfmSibs!IDFather;
> >
> > This query works well enough when run directly as a query (I get[/color][/color]
prompted[color=blue][color=green]
> > to input the "Forms!sfmSibs!IDFather"), but not when run from the
> > main form. I have also tried variations such as[/color]
> "[Forms].[sfmSibs]![IDFather]"[color=green]
> > in the query with the same result.
> >
> > In the RecordSource expression builder, I have:
> > "=qryFindFatherName!FirstName & " " & qryFindFatherName!Surname.
> >
> > I think that the problem is that I am not correctly specifying the[/color][/color]
control[color=blue][color=green]
> > for IDFather in the subform row for the WHERE statement in the query.
> > What do you think?[/color]
>
> A VBA solution would also be helpful as I also want to translate the Sex
> value (Male/Female) to Relationship
> (Brother/Half-Brother/Sister/Half-Sister) in the Siblings subform. Once I
> know how to access the values returned in each row, translating Sex to
> Relationship will be fairly simple VBA.
>
> Many thanks again!
> --
> Cheers,
> Lyn.
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:40cdad31$0$28941$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=green]
> > Hi Lyn
> >
> > Just back, and have had a chance to try what you are doing. The problem[/color]
> you[color=green]
> > are seeing is a bug in Access.
> >
> > Although you have saved the form with *nothing* in the
> > LinkMasterFields/LinkChildFields of your subform, Access is WRONGLY
> > assigning values to these properties when you assign the RecordSource of[/color]
> the[color=green]
> > subform. The result is that the only subform record that can match your[/color]
> main[color=green]
> > form record is itself! This is completely the wrong result for what you[/color]
> want[color=green]
> > (i.e. all the *other* siblings except the person themself).
> >
> > Even if you clear the automatic assignment to these properties, Access[/color]
> will[color=green]
> > create the assignment again as soon as you move record in the main form.
> >
> > There is a sample of this bug at this address:
> > http://allenbrowne.com/BadSubform.zip
> > I think you will find that this is exactly the behaviour you have been
> > struggling with.
> >
> > You can work around the behaviour by explicitly reassigning an empty[/color]
> string[color=green]
> > to the subform's LinkMasterFields and LinkChildFields properties in the[/color]
> main[color=green]
> > form's Current event, after you assign the RecordSource to the subform.[/color]
> It's[color=green]
> > an extremely inelegant workaround, but it does appear to let you have[/color][/color]
the[color=blue][color=green]
> > sibblings in your subform.
> >
> > Looks like another Access bug that needs to be written up.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Lyn" <lhancock@ihug.com.au> wrote in message
> > news:cajg57$73p$1@lust.ihug.co.nz...[color=darkred]
> > > Allen, I hope you enjoyed your break.
> > >
> > > I have had no luck with the VBA, but Helen has emailed me a solution[/color][/color]
> which[color=green][color=darkred]
> > > uses neither VBA nor a query (thanks again Helen). It just inserts an[/color][/color]
> SQL[color=green][color=darkred]
> > > Select statement directly into the RecordSource of the subform. And[/color][/color][/color]
it[color=blue][color=green][color=darkred]
> > > works fine. The statement is:
> > >
> > > SELECT * FROM Person WHERE
> > > ((Person.IDFather=[Forms]![frmMainForm]![IDFather]) OR
> > > (Person.IDMother=[Forms]![frmMainForm]![IDMother]))
> > > AND (Not (Person.IDperson=[Forms]![frmMainForm]![IDperson]));
> > >
> > > While I hadn't thought of inserting the SQL directly into[/color][/color][/color]
RecordSource,[color=blue]
> I[color=green][color=darkred]
> > > did think that I had tried this as a Query. However, I have tried so[/color][/color]
> many[color=green][color=darkred]
> > > different things trying to resolve this that I can't be sure. In the[/color][/color]
> end,[color=green][color=darkred]
> > > it was certainly simple enough.
> > >
> > > I can't help thinking that the solution via VBA is going to be simple[/color][/color]
> too.[color=green][color=darkred]
> > > Having come this far, I would still like to find the VBA solution, if[/color][/color]
> only[color=green][color=darkred]
> > > as a learning exercise. So if you still have the patience, I would[/color][/color][/color]
like[color=blue][color=green]
> > to[color=darkred]
> > > continue.
> > >
> > > One other thing. Rather than displaying the IDFather and IDMother[/color][/color]
> values[color=green]
> > in[color=darkred]
> > > the Continuous Feeds subform, I would like to translate each into a[/color][/color][/color]
text[color=blue][color=green][color=darkred]
> > > field inserted into the row (eg, [FirstName] & " " & [Surname]).[/color][/color][/color]
Using[color=blue][color=green][color=darkred]
> > > Helen's solution, I have created a text control in the subform row for
> > > Father. In the RecordSource for the control, I have firstly inserted[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> > > simple SQL statement (which didn't work -- gave me "#Name?"), and then[/color][/color]
> put[color=green][color=darkred]
> > > the same SQL into a Query (qryFindFatherName) and specified the query[/color][/color][/color]
as[color=blue][color=green]
> > the[color=darkred]
> > > RecordSource for the control (with the same result). The query was:
> > >
> > > SELECT * FROM Person WHERE Person.IDPerson=Forms!sfmSibs!IDFather;
> > >
> > > This query works well enough when run directly as a query (I get[/color][/color]
> prompted[color=green]
> > to[color=darkred]
> > > input the "Forms!sfmSibs!IDFather"), but not when run from the main[/color][/color]
> form.[color=green]
> > I[color=darkred]
> > > have also tried variations such as "[Forms].[sfmSibs]![IDFather]" in[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > query with the same result.
> > >
> > > In the RecordSource expression builder, I have:
> > > "=qryFindFatherName!FirstName & " " & qryFindFatherName!Surname.
> > >
> > > I think that the problem is that I am not correctly specifying the[/color][/color]
> control[color=green][color=darkred]
> > > for IDFather in the subform row for the WHERE statement in the query.[/color]
> > What[color=darkred]
> > > do you think? Helen, please feel free to comment also.
> > >
> > > Thanks again.
> > >
> > > --
> > > Cheers,
> > > Lyn.
> > >
> > > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> > > news:40c99b29$0$24181$5a62ac22@freenews.iinet.net. au...
> > > > Lyn, I'm guessing that Access is automatically setting the
> > > > LinkMasterFields/LinkChildFields properties of the subform control.[/color][/color]
> You[color=green][color=darkred]
> > > > don't want that, because you are programmatically assigning the[/color][/color]
> records.[color=green][color=darkred]
> > > >
> > > > Open the main form in design view.
> > > > Right-click the edge of the subform control, and choose Properties.
> > > > On the Data tab of the Properties box, locate the LinkMasterFields
> > > control,
> > > > and delete the IDPerson entry. Delete the entry for LinkChildFields[/color][/color][/color]
as[color=blue][color=green][color=darkred]
> > > well.
> > > >
> > > > I'm going to be away for the weekend, but will check back on Tuesday[/color][/color]
> to[color=green][color=darkred]
> > > see
> > > > if this solved your issue.[/color][/color][/color]

Helen, apologies but I did not see your latest post before I replied to
Allen. I have in fact got the VBA side of the Siblings query (and a similar
Children query) working with code very similar to what you have suggested.

Regarding my new query, it seems that you and Allen are both on the same
page, so I will work on that now and let you both know how it turns out.

Thanks again to you both.
--
Cheers,
Lyn.

"Helen Wheels" <helenwheelss@yahoo.com.au> wrote in message
news:40CE643A.7090401@yahoo.com.au...[color=blue]
> Getting there - more below!
>
> Lyn wrote:[color=green]
> > Allen, I hope you enjoyed your break.
> >
> > I have had no luck with the VBA, but Helen has emailed me a solution[/color][/color]
which[color=blue][color=green]
> > uses neither VBA nor a query (thanks again Helen). It just inserts an[/color][/color]
SQL[color=blue][color=green]
> > Select statement directly into the RecordSource of the subform. And it
> > works fine. The statement is:
> >
> > SELECT * FROM Person WHERE
> > ((Person.IDFather=[Forms]![frmMainForm]![IDFather]) OR
> > (Person.IDMother=[Forms]![frmMainForm]![IDMother]))
> > AND (Not (Person.IDperson=[Forms]![frmMainForm]![IDperson]));
> >
> > While I hadn't thought of inserting the SQL directly into RecordSource,[/color][/color]
I[color=blue][color=green]
> > did think that I had tried this as a Query. However, I have tried so[/color][/color]
many[color=blue][color=green]
> > different things trying to resolve this that I can't be sure. In the[/color][/color]
end,[color=blue][color=green]
> > it was certainly simple enough.
> >
> > I can't help thinking that the solution via VBA is going to be simple[/color][/color]
too.[color=blue][color=green]
> > Having come this far, I would still like to find the VBA solution, if[/color][/color]
only[color=blue][color=green]
> > as a learning exercise. So if you still have the patience, I would like[/color][/color]
to[color=blue][color=green]
> > continue.[/color]
>
> Yes, I'd agree that setting the subform's record source using VBA
> code running on the main form's Current event is a better way to
> go. Although the quick and dirty solution I sent you earlier
> works, it's not very easily re-usable. What are the odds you'll
> want to display grandchildren/nieces & nephews etc later? If you
> do, you can re-use the same subform but set a different
> recordsource for it from the main form.
>
> So, combining some bits of mine and some bits of Allen's
> responses, try putting the following in your main form's
> OnCurrent event (watch out for possible line wrapping in your
> mail reader and make sure I got the name of your subform right!):
>
> 'CODE STARTS HERE:
> Dim mySQL As String
>
> 'Create the SQL to select people with either the same mother or
> 'the same father as the current record in the main form.
> 'We deal with null/unknown parent IDs by checking whether theyre
> 'equal to an ID number that no person will ever have, i.e. 0
>
> mySQL = "SELECT * FROM Person " & _
> "WHERE ((Person.IDFather=" & Nz(Me![IDFather], 0) & ") " & _
> "OR (Person.IDMother=" & Nz(Me![IDMother], 0) & ")) " & _
> "AND (Not (Person.IDperson=" & Me![IDperson] & "));"
>
> 'Assign that SQL string as the record source of the subform
> Me![sfmSibs].Form.RecordSource = mySQL
>
> 'Deal with Access's rude habit of filling in the link
> 'master/child fields with values you didn't ask for:
> Me![sfmSibs].LinkChildFields = ""
> Me![sfmSibs].LinkMasterFields = ""
>
> 'CODE ENDS HERE
>
>[color=green]
> >
> > One other thing. Rather than displaying the IDFather and IDMother[/color][/color]
values in[color=blue][color=green]
> > the Continuous Feeds subform, I would like to translate each into a text
> > field inserted into the row (eg, [FirstName] & " " & [Surname]). Using
> > Helen's solution, I have created a text control in the subform row for
> > Father. In the RecordSource for the control, I have firstly inserted a
> > simple SQL statement (which didn't work -- gave me "#Name?"), and then[/color][/color]
put[color=blue][color=green]
> > the same SQL into a Query (qryFindFatherName) and specified the query as[/color][/color]
the[color=blue][color=green]
> > RecordSource for the control (with the same result). The query was:
> >[/color]
>
> I'd probably join the person table to itself in the subform
> record source, once to get the mother's details and a second time
> for the father's. The SQL for the subform's recordsource would
> then be something like:
>
> mySQL = "SELECT Person.*, " & _
> "father.name AS fathername, mother.name AS mothername " & _
> "FROM (Person LEFT JOIN person AS father " & _
> "ON Person.IDfather = father.idPerson) " & _
> "LEFT JOIN person AS mother " & _
> "ON Person.IDmother = mother.idPerson " & _
> "WHERE ((Person.IDFather=" & Nz(Me![IDFather], 0) & ") " & _
> "OR (Person.IDMother=" & Nz(Me![IDMother], 0) & ")) " & _
> "AND (Not (Person.IDperson=" & Me![IDperson] & "));"
>
>
> I'll bet I got at least one typo somewhere in the above, but
> hopefully this gets you a bit further along the track. Good luck!
> Helen
>[/color]

Allen, Helen,
Thanks to you both, I now have the Siblings subform working as desired.

I really appreciate your patience and perseverance in seeing this through
with me.

Best regards.
--
Cheers,
Lyn.

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:40ceb146$0$28950$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=blue]
> Hi Lyn
>
> You should be able to use a query statement like this one:
>
> SELECT tblPerson.*,
> Trim([Father].[FirstName] & " " & [Father].[Surname]) AS FatherName,
> Trim([Mother].[FirstName] & " " & [Mother].[Surname]) AS MotherName
> FROM (tblPerson LEFT JOIN tblPerson AS Father ON tblPerson.IDFather =
> Father.IDPerson)
> LEFT JOIN tblPerson AS Mother ON tblPerson.IDMother = Mother.IDPerson;
>
> You can paste that into SQL View of a query (View menu in query design),[/color]
to[color=blue]
> see how it works, and modify it to suit.
>
> Essentially, the query contains 3 copies of the same table. Two copies are
> aliased so they are meaningful. (Set an Alias in the Properties box - view
> menu).
>
> We use outer joins in case the parents are unknown. (Double-click the join
> lines to set the join type.) Provided you are not trying to enter the
> parents names, you should find that this query is updatable, so suitable[/color]
for[color=blue]
> your form.
>
> I think Helen is suggesting something similar.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>[/color]

Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

re: Recordset in subform based on field in parent form

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about