473,889 Members | 1,497 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Recordset in subform based on field in parent form

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

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

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

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

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

I substituted:

WHERE Person.IDFather = frmMainForm!IDF ath OR Person.IDMother =
frmMainForm!IDM oth ...

I obviously have this syntax wrong, because when I run the main form, for
each record I get msgboxes prompting for the values frmMainForm!IDF ath and
frmMainForm!IDM oth. 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.
Nov 13 '05 #1
25 10292
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" <lh******@ihug. com.au> wrote in message
news:c9******** **@lust.ihug.co .nz...
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!IDF ath OR Person.IDMother =
frmMainForm!IDM oth ...

I obviously have this syntax wrong, because when I run the main form, for
each record I get msgboxes prompting for the values frmMainForm!IDF ath and
frmMainForm!IDM oth. 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.

Nov 13 '05 #2
Hi Lyn

You could try:
WHERE (Person.IDFathe r = Nz(Forms!frmMai nForm!IDFath,0) )
OR (Person.IDMothe r = Nz(Forms!frmMai nForm!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" <lh******@ihug. com.au> wrote in message
news:c9******** **@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 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!IDF ath OR Person.IDMother =
frmMainForm!IDM oth ...

I obviously have this syntax wrong, because when I run the main form, for
each record I get msgboxes prompting for the values frmMainForm!IDF ath and
frmMainForm!IDM oth. 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.

Nov 13 '05 #3
Lyn wrote:
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!IDF ath OR Person.IDMother =
frmMainForm!IDM oth ...
If you make the record source for your subform something like
this it should work:

SELECT * FROM Person WHERE
((Person.IDFath er=[Forms]![frmMainForm]![IDFather]) OR
(Person.IDMothe r=[Forms]![frmMainForm]![IDMother]))
AND (Not (Person.IDperso n=[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.

I obviously have this syntax wrong, because when I run the main form, for
each record I get msgboxes prompting for the values frmMainForm!IDF ath and
frmMainForm!IDM oth. 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?


this shouldn't be any problem.

Nov 13 '05 #4
Lyn
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" <Al*********@Se eSig.Invalid> wrote in message
news:40******** **************@ freenews.iinet. net.au...
Hi Lyn

You could try:
WHERE (Person.IDFathe r = Nz(Forms!frmMai nForm!IDFath,0) )
OR (Person.IDMothe r = Nz(Forms!frmMai nForm!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" <lh******@ihug. com.au> wrote in message
news:c9******** **@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 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!IDF ath OR Person.IDMother =
frmMainForm!IDM oth ...

I obviously have this syntax wrong, because when I run the main form, for each record I get msgboxes prompting for the values frmMainForm!IDF ath and frmMainForm!IDM oth. 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.


Nov 13 '05 #5
Lyn
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" <no**********@s mittysinet.com> wrote in message
news:GP******** ************@co mcast.com...
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" <lh******@ihug. com.au> wrote in message
news:c9******** **@lust.ihug.co .nz...
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!IDF ath OR Person.IDMother =
frmMainForm!IDM oth ...

I obviously have this syntax wrong, because when I run the main form, for each record I get msgboxes prompting for the values frmMainForm!IDF ath and frmMainForm!IDM oth. 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.


Nov 13 '05 #6
Lyn
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" <he**********@y ahoo.com.au> wrote in message
news:40******** ******@yahoo.co m.au...
Lyn wrote:
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!IDF ath OR Person.IDMother =
frmMainForm!IDM oth ...


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

SELECT * FROM Person WHERE
((Person.IDFath er=[Forms]![frmMainForm]![IDFather]) OR
(Person.IDMothe r=[Forms]![frmMainForm]![IDMother]))
AND (Not (Person.IDperso n=[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.

I obviously have this syntax wrong, because when I run the main form, for each record I get msgboxes prompting for the values frmMainForm!IDF ath and frmMainForm!IDM oth. 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?


this shouldn't be any problem.

Nov 13 '05 #7
Lyn
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.I DPerson (the autonum record ID)
which is joined to Person_Sib.IDFa ther. 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" <Al*********@Se eSig.Invalid> wrote in message
news:40******** **************@ freenews.iinet. net.au...
Hi Lyn

You could try:
WHERE (Person.IDFathe r = Nz(Forms!frmMai nForm!IDFath,0) )
OR (Person.IDMothe r = Nz(Forms!frmMai nForm!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" <lh******@ihug. com.au> wrote in message
news:c9******** **@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 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!IDF ath OR Person.IDMother =
frmMainForm!IDM oth ...

I obviously have this syntax wrong, because when I run the main form, for each record I get msgboxes prompting for the values frmMainForm!IDF ath and frmMainForm!IDM oth. 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.


Nov 13 '05 #8
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.RecordSou rce = "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" <lh******@ihug. com.au> wrote in message
news:c9******** **@lust.ihug.co .nz...
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.I DPerson (the autonum record ID) which is joined to Person_Sib.IDFa ther. 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" <Al*********@Se eSig.Invalid> wrote in message
news:40******** **************@ freenews.iinet. net.au...
Hi Lyn

You could try:
WHERE (Person.IDFathe r = Nz(Forms!frmMai nForm!IDFath,0) )
OR (Person.IDMothe r = Nz(Forms!frmMai nForm!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" <lh******@ihug. com.au> wrote in message
news:c9******** **@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 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!IDF ath OR Person.IDMother =
frmMainForm!IDM oth ...

I obviously have this syntax wrong, because when I run the main form, for each record I get msgboxes prompting for the values frmMainForm!IDF ath and frmMainForm!IDM oth. 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.

Nov 13 '05 #9
Lyn
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.SurnameB irth, Person.FirstNam e, " & _
"Person.DOB , Person.Sex, " & _
"Person.IDPerso n FROM Person " & _
"WHERE Person.IDFather = " & Me![IDFather] & ";"
Me.[sfmSibs].Form.RecordSou rce = 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.RecordSou rce = 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" <Al*********@Se eSig.Invalid> wrote in message
news:40******** *************** @freenews.iinet .net.au...
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.RecordSou rce = "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" <lh******@ihug. com.au> wrote in message
news:c9******** **@lust.ihug.co .nz...
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.I DPerson (the autonum record

ID)
which is joined to Person_Sib.IDFa ther. 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" <Al*********@Se eSig.Invalid> wrote in message
news:40******** **************@ freenews.iinet. net.au...
Hi Lyn

You could try:
WHERE (Person.IDFathe r = Nz(Forms!frmMai nForm!IDFath,0) )
OR (Person.IDMothe r = Nz(Forms!frmMai nForm!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" <lh******@ihug. com.au> wrote in message
news:c9******** **@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 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!IDF ath OR Person.IDMother =
> frmMainForm!IDM oth ...
>
> I obviously have this syntax wrong, because when I run the main
form, for
> each record I get msgboxes prompting for the values
frmMainForm!IDF ath and
> frmMainForm!IDM oth. 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.


Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
3921
by: Bruno Luis | last post by:
Hello I'm using A97 and i'm having some trouble with creating a recordset with data from two different databases. I connect to a remote database and make this data the default source for my continuous form (Set me.recordset = rs). The problem is: one of the fields of the remote database is a foreign key (two digit number), and the table with the correspondence is in my local database... i have no way of altering the remote database....
2
4408
by: Galina | last post by:
Hello I have an application in MS Access 2000. I have a form, which includes a subform. The subform is based on a table, but locked for any editing. There is Edit button. When clicked, it starts a separate unbound form with all the fields, which are on the subform. The fields are filled in with data programmatically. User edits. Edited fields are copied into fields on the subform programmatically as well. I need to catch change in one of...
0
2362
by: CSDunn | last post by:
Hello, In Access ADP's that connect to SQL Server databases, any time I have a situation where I have a combo box in a main form that looks up a record in a subform, the subform record source has to be based on either a View or a Table. I can almost always use a View, and it helps to do this since I can have better control over the size of the RecordSet of the subform. There are times when the use of a Stored Procedure would give me...
4
7030
by: Dave Boyd | last post by:
Hi, I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the user enters the last qualifying field on the main form. In one case this works fine, the subform shows the data the user wants to update -- which means showing all the data put in previously (ie showing this via the requery and the continuous...
3
2796
by: shumaker | last post by:
This code from the subform works for getting the value of a field of the main form named "WorkSheet": MsgBox Form_WorkSheet.Recordset.Fields("Clerk").Value Each record in the mainform datasheet called "WorkSheet" has a subform. I am trying to get the controls on the subform to reference the fields in the main form, because I think that it will resolve some of my write conflict problems by not having a recordset for the subform, and...
6
9362
by: GSteven | last post by:
(as formerly posted to microsoft.public.access.forms with no result) I've created a continuous form which is based on a straightforward table (ex - customers - 100 records). On the form there is a checkbox with a control source named "MARK" (boolean) from customer table. I can check and uncheck individual records fine. Then I created 2 command buttons named "Select All" and "Deselect All". The Onclick property of these buttons runs code...
6
4509
by: CPAccess | last post by:
How do I maintain referential integrity between a main form and a subform, each based upon different (but joined with integrity enforced) table? Here's the situation: I have two tables: tblContracts and tblPayments. tblContracts has an autonumber field called IDKey as its primary key. tblPayments also has an IDKey field (Integer datatype). The two tables are linked in a one-to-many relationship on the field IDKey with referential integrity...
14
7765
by: ml_sauls | last post by:
I've built a system to enter and manage purchase orders. This is in use by >10 clients. Some use it in Access 97, most are in A2k. About half use it through a Citrix implementation. It is separated into a front end and 3 back ends (1 network for permanent storage, 1 local for temp storage, and 1 local for storage for particular user). One particular form is failing for a single client (Access 2000, using Citrix). This form is a form...
7
6222
by: dscarbor | last post by:
I have a simple form with 4 fields, and a subform that retrieves records that are potential matches based on a query. The user will use the ID from the subform record and enter it into one of the fields on the main form (this is how the match will be stored). Is there a way using a button/macro to populate the parent form's field with the ID of the subform record that is currently selected? For example, the subform loads 7 records. The...
0
9962
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
10889
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10442
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9609
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7993
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5829
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6029
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4647
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3256
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.